package cn.com.nes.site.module.dao;

import cn.com.nes.common.util.*;
import cn.com.nes.common.util.id.IdUtils;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.module.dao.crm.CustomerDaoImpl;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

@Slf4j
@Repository
public class ProjectDaoImpl extends BaseConnDaoImpl {

    @Resource
    private CustomerDaoImpl customerDaoImpl;

    public PageData updateProjectAuditStatus(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        param.put("tableName", "t_work_order_info");
        param.put("keys", "work_order_id");
        try {
            String auditStatus = (String) param.get("auditStatus");
            String work_order_id = (String) param.get("work_order_id");
            param.put("auditTime", DBUtils.getCurTime());
            param.put("auditUserId", req.getUinfo().getId());

            if (StringUtils.isEmpty(auditStatus)) {
                res.setRetCode(9);
                res.setRetMessage("审核状态不能为空！");
            }
            if (StringUtils.isEmpty(work_order_id)) {
                res.setRetCode(9);
                res.setRetMessage("工单编号不能为空！");
            }
            res = updateTableData(conn, req);
            if (res.getRetCode() != 0) {
                return res;
            }

            if ("1".equals(auditStatus)) {
                updateProjectStatus(conn, (String) param.get("project_uuid"), "07");
                //插入项目完成日志
                LinkedHashMap<String, Object> e = new LinkedHashMap<String, Object>();
                e.put("tableName", "t_work_order_info");
                e.put("keys", "work_order_id");
                e.put("work_order_id", param.get("work_order_id"));
                PageData resTmp = getDetailsByKey(conn, e);
                if (resTmp.getRetCode() != 0) {
                    return resTmp;
                }

                e = new LinkedHashMap<String, Object>();
                e.put("tableName", "t_project_record");
                e.put("project_uuid", (String) resTmp.getData().get("dd_id"));
                e.put("project_status", "07");
                e.put("remark", "项目完成，审核通过");
                req.setReqData(e);
                resTmp = insertTableData(conn, req);
                if (resTmp.getRetCode() != 0) {
                    return resTmp;
                }
            }

            if (res.getRetCode() == 0) {
                //插入日志
                insertProjectLog(conn, req, work_order_id, "2", "工单审核", "进行了工单审核");
                res.setRetCode(0);
                res.setRetMessage("OK");
            }
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }


    public PageData addProjectCheck(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        param.put("tableName", "t_project_check");
        try {
            String check_no = (String) param.get("check_no");
            String check_type = (String) param.get("check_type");
            if (StringUtils.isEmpty(check_no)) {
                check_no = DBUtils.getDjbh(req.getUinfo().getCompanyId(), "CHK");
                param.put("check_no", check_no);
            }

            param.put("company_id", req.getUinfo().getCompanyId());

            String uuid = (String) param.get("uuid");
            if (StringUtils.isEmpty(uuid)) {
                res = insertTableData(conn, req);
                //插入项目记录表
                if (res.getRetCode() != 0) {
                    return res;
                }

                //检查之后，更改项目状态，待验收
                updateProjectStatus(conn, (String) param.get("project_uuid"), "06");

                //更新商机阶段
                if (StringUtils.isEmpty((String) param.get("contract_no"))) {
                    updateOpportunityStatus(conn, (String) param.get("contract_no"), "06");
                }

                //插入日志
                String memo = "安全检查";
                String memoID = "3";
                if ("01".equals(check_type)) {
                    memo = "质量检查";
                    memoID = "4";
                }
                insertProjectLog(conn, req,(String) param.get("project_uuid"), memoID, memo, "进行了" + memo);

            } else {
                param.put("keys", "uuid");
                res = updateTableData(conn, req);
            }

            if (res.getRetCode() == 0) {

                res.setRetCode(0);
                res.setRetMessage("OK");
            }
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();

        } finally {
        }
        return res;
    }

    public PageData deleteData(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        try {

            res = deleteTableData(conn, req);

            if (res.getRetCode() == 0) {

                //更新商机阶段
                String tableName = (String) param.get("tableName");
                if ("t_project".equals(tableName)) { // 若是删除项目，修改商机状态
                    //生成待处理安装工单
                    LinkedHashMap<String, Object> e = new LinkedHashMap<String, Object>();
                    e.put("tableName", "t_project");
                    e.put("keys", "uuid");
                    e.put("uuid", param.get("uuid"));
                    PageData resTmp = getDetailsByKey(conn, e);
                    if (resTmp.getRetCode() != 0) {
                        return resTmp;
                    }
                    String contract_no = (String) resTmp.getData().get("contract_no");
                    if (!StringUtils.isEmpty(contract_no)) {
                        updateOpportunityStatus(conn, contract_no, "05");
                    }
                }


                res.setRetCode(0);
                res.setRetMessage("OK");
            }
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();

        } finally {
        }
        return res;
    }


    public PageData addProject(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        param.put("tableName", "t_project");
        try {
            //拓展字段用
            String jsondata = (String) param.get("jsondata");
            jsondata = URLDecoder.decode(jsondata, "UTF-8");
            param.put("jsondata", jsondata);
            //拓展字段用

            String project_id = (String) param.get("project_id");
            if (StringUtils.isEmpty(project_id)) {
                param.put("project_id", DBUtils.getDjbh(req.getUinfo().getCompanyId(), "PRO"));
            }

            param.put("company_id", req.getUinfo().getCompanyId());

            String uuid = (String) param.get("uuid");
            if (StringUtils.isEmpty(uuid)) {
                res = insertTableData(conn, req);
                //插入项目记录表
                if (res.getRetCode() != 0) {
                    return res;
                }

                HashMap<String, Object> e = new LinkedHashMap<String, Object>();
                e.put("tableName", "t_project_record");
                e.put("project_uuid", res.getKey());
                e.put("project_status", "01");
                e.put("remark", "待排期");
                req.setReqData(e);
                PageData resTmp = insertTableData(conn, req);
                if (resTmp.getRetCode() != 0) {
                    return resTmp;
                }

                //插入项目日志表
                insertProjectLog(conn, req, res.getKey(), "1", "新建项目", "新建一个项目");


            } else {
                param.put("keys", "uuid");
                res = updateTableData(conn, req);
            }

            if (res.getRetCode() == 0) {
                res.setRetCode(0);
                res.setRetMessage("OK");
            }
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {
        }
        return res;
    }


    public PageData getProjectHomePage(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();
        Map<String, Object> extData = new LinkedHashMap<String, Object>();
        try {
            //获取我的代办事项
            sb.delete(0, sb.length());
            sb.append(" select b.`value` ,b.`name`,count(a.project_status) cnt from t_param b LEFT JOIN t_project a on a.deleted = 0 and a.project_status=b.`value` and (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')   ");

            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));
            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }

            sb.append(" where b.param_type='90' ");


            sb.append(" GROUP BY b.`value` order BY b.`value` ");
            List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});

            extData.put("topData", list);

            //获取今日动态
            sb.delete(0, sb.length());
            sb.append(" select date_format(a.createTime,'%Y-%m-%d %T') createTime,a.link_id,a.link_type,a.title,b.areacode,b.username from t_project_log a INNER JOIN t_project p on p.uuid = a.link_id INNER JOIN t_user b on a.createUid=b.id where p.deleted = 0 and b.companyId like concat(?,'%') ");

            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "b.areacode", new String[]{"a.createUid", req.getUinfo().getId()}));

            sb.append("  and a.createTime >= '").append(DBUtils.getDate(-1)).append("' ");
            sb.append(" order BY a.createTime desc limit 100 ");
            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            extData.put("dynamicCnt", list.size());
            extData.put("dynamic", list);

            //获取数据看板汇总数据
            sb.delete(0, sb.length());
            sb.append(" select count(1) allcnt,sum(if(project_status in ('04','05','06'),1,0)) inrunning,sum(if(a.createTime>date_sub(now(), INTERVAL 30 DAY),1,0)) cnt30, sum(if(is_payment='1',1,0)) payment, sum(if(is_payment='0' and a.project_status='07' ,1,0)) notpayment, ");
            sb.append(" sum(if(b.end_time<now()  and a.project_status<'07',1,0)) out_of_date_cnt ");
            sb.append(" from t_project a LEFT JOIN t_project_scheduling b on a.uuid=b.project_uuid where a.deleted = 0 and (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  ");

            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));

            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }
            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            if (list.size() > 0) {
                extData.put("projectInfo", list.get(0));
            }

            //按天获取新建项目曲线图
            sb.delete(0, sb.length());
            sb.append(" SELECT date_format(a.createTime,'%Y-%m-%d') createTime,sum(if(a.project_type='01',1,0)) civilCnt,sum(if(a.project_type<>'01',1,0)) commercialCnt  from t_project a  where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));


            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }

            //本月散点图
            sb.append(" and a.createTime>= '").append(DBUitl.getDate(-30)).append("' ");

            sb.append("  group by date_format(a.createTime,'%Y-%m-%d')");
            sb.append("  order by a.createTime ");
            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            extData.put("newProjectLine", list);

            //按天获取回款项目曲线图
            sb.delete(0, sb.length());
            sb.append(" SELECT date_format(a.createTime,'%Y-%m-%d') createTime,sum(if(a.is_payment='1',1,0)) is_payment,sum(if(a.is_payment='0',1,0)) is_notpayment  from t_project a  where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  ");

            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));

            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }

            //本月散点图
            sb.append(" and a.createTime>= '").append(DBUitl.getDate(-30)).append("' ");

            sb.append("  group by date_format(a.createTime,'%Y-%m-%d')");
            sb.append("  order by a.createTime ");
            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            extData.put("paymentProjectLine", list);


            //数据散点图
            sb.delete(0, sb.length());
            sb.append(" SELECT a.uuid project_uuid, a.charge,a.project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime  FROM 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));
            sb.append(" and a.latitude is not null and a.longitude is not null ");
            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }

            //本月散点图
            sb.append(" and a.createTime>= '").append(DBUitl.getDate(-30)).append("' ");

            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            extData.put("mapData", list);


            //施工中项目列表
            sb.delete(0, sb.length());
            sb.append(" SELECT a.uuid project_uuid,	a.project_id project_no,a.charge,a.project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name,e.departmentName  FROM 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  LEFT JOIN t_project_scheduling c on a.uuid=c.project_uuid  LEFT JOIN t_department e on a.department_id=e.id  " + "where a.deleted = 0 and (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  ");
            //sb.append("  and a.area_code  like '").append(XzqhUtil.getQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode())).append("%' ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));
            sb.append(" and a.latitude is not null and a.longitude is not null and a.project_status<>'07' ");
            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }


            sb.append(" limit 4 ");

            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            extData.put("constructioningList", list);


            res.setExtData(extData);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }


    public PageData getProgressOverview(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();
        Map<String, Object> extData = new LinkedHashMap<String, Object>();
        try {

            String period = (String) req.getReqData().get("period");
            if (StringUtils.isEmpty(period)) {
                period = "9";
            }

            String days = "30";
            switch (period) {
                case "1":
                    days = "1";
                    break;
                case "2":
                    days = "2";
                    break;
                case "3":
                    days = "7";
                    break;
                case "4":
                    days = "30";
                    break;
                case "5":
                    days = "365";
                    break;
                default:
                    break;
            }

            //获取我的代办事项
            sb.delete(0, sb.length());
            sb.append(" SELECT ");
            sb.append(" sum(if ((a.planned_end_time<now() and a.project_status='01'  or a.project_status='02' and b.end_time<now()),1,0)) laster_cnt, ");
            sb.append(" sum(if(a.project_status in ('04','05','06'),1,0)) running_cnt	, ");
            sb.append(" sum(if(b.end_time<now()  and a.project_status<'07',1,0)) out_of_date_cnt, ");
            sb.append("  sum(if(a.createTime>=date_sub(date(now()), INTERVAL ")
                    .append(days)
                    .append(" DAY),1,0)) cnt30, ");

            sb.append("  sum(if(c.createTime>date_sub(now(), INTERVAL 30 DAY),1,0)) cnt30_finish ");


            sb.append(" FROM t_project a left JOIN t_project_scheduling b on a.uuid=b.project_uuid ");

            sb.append("  left JOIN t_project_record c on a.uuid=c.project_uuid and c.project_status='07' ");

            sb.append(" where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("') ");

            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));

            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }

            List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});

            extData.put("topData", list);


            //按天获取新建项目曲线图
            sb.delete(0, sb.length());

            sb.append(" SELECT a.uuid project_uuid, a.project_id,a.project_name,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T')  planned_end_time,date_format(b.start_time,'%Y-%m-%d %T') start_time,date_format(b.end_time,'%Y-%m-%d %T') end_time,a.project_status, ");
            sb.append(" if(a.project_status='01',datediff(now(),a.planned_end_time),datediff(now(),b.end_time) ) overDate ");
            sb.append(" from  t_project a left JOIN t_project_scheduling b on a.uuid=b.project_uuid ");
            sb.append(" where (a.planned_end_time<now() and a.project_status='01'  or a.project_status<'07' and b.end_time<now()) and (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  ");

            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));

            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }
            sb.append("  order by b.end_time,a.planned_end_time  limit 20 ");
            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            extData.put("outDateProjectLine", list);


            //施工中项目列表
            sb.delete(0, sb.length());
            sb.append(" SELECT a.uuid project_uuid,	a.project_id project_no,a.charge,a.project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name ,m.name project_type_name,n.name service_mode_name,x.name project_level_name,a.project_level   FROM 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  ");
            sb.append(" INNER JOIN t_param m on a.project_type=m.`value` and m.param_type='92'  ");
            sb.append(" INNER JOIN t_param n on a.service_mode=n.`value` and n.param_type='91'  ");
            sb.append(" INNER JOIN t_param x on a.project_level=x.`value` and x.param_type='93'  ");
            sb.append(" LEFT JOIN t_project_scheduling c on a.uuid=c.project_uuid where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  ");
            sb.append(" and a.project_status in ('04','05','06') ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));
            sb.append(" and a.latitude is not null and a.longitude is not null ");
            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }
            sb.append(" order by  a.createTime desc ");
            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            extData.put("constructioningList", list);

            //待排期项目列表
            sb.delete(0, sb.length());
            sb.append(" SELECT 	a.uuid project_uuid,a.project_id project_no,a.charge,a.project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name ,m.name project_type_name,n.name service_mode_name,x.name project_level_name,a.project_level   FROM 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  ");
            sb.append(" INNER JOIN t_param m on a.project_type=m.`value` and m.param_type='92'  ");
            sb.append(" INNER JOIN t_param n on a.service_mode=n.`value` and n.param_type='91'  ");
            sb.append(" INNER JOIN t_param x on a.project_level=x.`value` and x.param_type='93'  ");
            sb.append(" LEFT JOIN t_project_scheduling c on a.uuid=c.project_uuid where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  ");
            sb.append(" and a.project_status in ('01') ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));
            sb.append("  and a.latitude is not null and a.longitude is not null ");
            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }
            sb.append(" order by  a.createTime desc ");
            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            extData.put("watingSchedulingList", list);


            //待分配项目列表
            sb.delete(0, sb.length());
            sb.append(" SELECT 	a.uuid project_uuid,a.project_id project_no,a.charge,a.project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name ,m.name project_type_name,n.name service_mode_name,x.name project_level_name,a.project_level   FROM 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90' ");

            sb.append(" INNER JOIN t_param m on a.project_type=m.`value` and m.param_type='92'  ");
            sb.append(" INNER JOIN t_param n on a.service_mode=n.`value` and n.param_type='91'  ");
            sb.append(" INNER JOIN t_param x on a.project_level=x.`value` and x.param_type='93'  ");
            sb.append(" LEFT JOIN t_project_scheduling c on a.uuid=c.project_uuid where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  ");

            sb.append(" and a.project_status in ('01','02','03','04','05','06') ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));

            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }
            sb.append(" order by  a.createTime desc ");

            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            extData.put("watingDistributionList", list);


            res.setExtData(extData);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    //项目排期
    public PageData addProjectScheduling(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        param.put("tableName", "t_project_scheduling");
        try {
            String createTime = (String) req.getReqData().get("createTime");
            String uuid = (String) param.get("uuid");
            if (StringUtils.isEmpty(uuid)) {
                res = insertTableData(conn, req);
                if (res.getRetCode() != 0) {
                    return res;
                }

                //插入项目记录表
                HashMap<String, Object> e = new LinkedHashMap<String, Object>();
                e.put("tableName", "t_project_record");
                e.put("project_uuid", param.get("project_uuid"));
                e.put("project_status", "02");
                e.put("remark", "待分配");
                req.setReqData(e);
                PageData resTmp = insertTableData(conn, req);
                if (resTmp.getRetCode() != 0) {
                    return resTmp;
                }

                //生成待处理安装工单
                e = new LinkedHashMap<String, Object>();
                e.put("tableName", "t_project");
                e.put("keys", "uuid");
                e.put("uuid", param.get("project_uuid"));
                resTmp = getDetailsByKey(conn, e);
                if (resTmp.getRetCode() != 0) {
                    return resTmp;
                }

                if (resTmp.getRetCode() == 0) {
                    // e.put("tableName", "t_work_order_info");
                    // e.put("work_order_id", DBUtils.getDjbh(req.getUinfo().getCompanyId(), "WOR"));
                    // e.put("company_id", req.getUinfo().getCompanyId());
                    // e.put("dd_id", param.get("project_uuid"));
                    // e.put("work_type_id", "1");
                    // e.put("contact", resTmp.getData().get("contact"));
                    // e.put("contact_phone", resTmp.getData().get("contact_phone"));
                    // e.put("address", resTmp.getData().get("project_address"));
                    // e.put("planned_start_time", resTmp.getData().get("planned_start_time"));
                    // e.put("planned_end_time", resTmp.getData().get("planned_end_time"));
                    // e.put("xzqhdm", resTmp.getData().get("area_code"));
                    // e.put("manufacture_id", resTmp.getData().get("manufacture_id"));
                    // e.put("describe", "创建安装工单");
                    // e.put("status", "01");
                    //
                    // e.put("createTime", createTime);
                    //
                    //
                    // req.setReqData(e);
                    // resTmp = insertTableData(conn, req);
                    // if (resTmp.getRetCode() != 0) {
                    //     return resTmp;
                    // }
                    // //插入工单记录
                    // e.put("describe", "工单待处理"); //待接单
                    // resTmp = addWorkOrderRecord(conn, req);
                    // if (resTmp.getRetCode() != 0) {
                    //     return resTmp;
                    // }
                    //
                    // res.setExtData(e.get("work_order_id"));
                    //插入项目日志表
                    insertProjectLog(conn, req, (String) param.get("project_uuid"), "1", "项目排期", "对项目进行了排期");

                }
            } else {
                param.put("keys", "uuid");
                param.put("uuid", param.get("scheduling_uuid"));
                res = updateTableData(conn, req);
            }
            if (res.getRetCode() == 0) {
                //更新项目状态
                updateProjectStatus(conn, (String) param.get("project_uuid"), "02");
                res.setRetCode(0);
                res.setRetMessage("OK");
            }
        } catch (Exception e) {
            e.printStackTrace();

            res.setRetCode(9);
            res.setRetMessage("处理失败");
        } finally {
        }
        return res;
    }

    public boolean updateProjectStatus(Connection conn, String uuid, String status) {
        try {
            update(conn, "update t_project set project_status = ? where uuid=? ", new Object[]{status, uuid});
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return false;
    }

    public boolean updateOpportunityStatus(Connection conn, String uuid, String status) {
        try {
            update(conn, "update opportunity set opportunitytype = ? where uuid=? ", new Object[]{status, uuid});
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return false;
    }


    public boolean insertProjectLog(Connection conn, DataReq req, String linkid, String linktype, String title,
            String remark) throws SQLException {
        Map<String, Object> param = req.getReqData();
        param.put("tableName", "t_project_log");
        param.put("link_id", linkid);
        param.put("link_type", linktype);
        param.put("title", title);
        param.put("remark", remark);
        insertTableData(conn, req);
        return true;
    }


    public PageData getProjectList(Connection conn, DataReq req) {
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        try {

            //是否是看板过来
            String queryType = (String) req.getReqData().get("queryType");
            String period = (String) req.getReqData().get("period"); // 周期，表示要查询的日期范围
            String opportunityId = (String) req.getReqData().get("businessId");
            String customerId = (String) req.getReqData().get("customerId");
            if (StringUtils.isEmpty(period)) {
                period = "9";
            }

            int days = 30;
            switch (period) {
                case "1":
                    days = 1;
                    break;
                case "2":
                    days = 2;
                    break;
                case "3":
                    days = 7;
                    break;
                case "4":
                    days = 30;
                    break;
                case "5":
                    days = 365;
                    break;
                default:
                    break;
            }

            if (!"9".equals(period) && "1".equals(queryType)) {//来自于数据一览跳转
                req.getReqData().put("start_date", DBUtils.getDate(0 - days));
                req.getReqData().put("end_date", DBUtils.getDate(0));
            }

            if (StringUtils.isEmpty(queryType)) {
                queryType = "1";
            }

            //施工中项目列表
            sb.delete(0, sb.length());
            sb.append("SELECT a.jsondata,a.templateId,c.uuid scheduling_uuid,a.contract_no,a.images,a.area_code,a.project_address,a.contact,a.contact_phone,a.service_mode,a.project_type,a.project_level,a.uuid project_uuid,a.uuid,a.project_id project_no,a.charge,a.project_id project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name,m.name project_type_name,n.name service_mode_name,x.name project_level_name,if(c.end_time<now() and a.project_status<>'07',1,0) isOver,a.remark,bim.bimId,a.investment_scale,a.manufacture_id,cmp.companyName manufacture_Name,cmp_dl.companyName companyName, a.opportunity_uuid ");
            sb.append(" from t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90' ");
            sb.append(" INNER JOIN t_param m on a.project_type=m.`value` and m.param_type='92'  ");
            sb.append(" INNER JOIN t_param n on a.service_mode=n.`value` and n.param_type='91'  ");
            sb.append(" INNER JOIN t_param x on a.project_level=x.`value` and x.param_type='93'  ");
            sb.append(" LEFT JOIN t_project_scheduling c on a.uuid=c.project_uuid ");
            sb.append(" left JOIN t_project_record rec on a.uuid=rec.project_uuid and rec.project_status='07' ");
            sb.append(" LEFT JOIN opportunity_bim bim on a.contract_no=bim.opportunityId ");
            sb.append(" LEFT JOIN t_company cmp on a.manufacture_id=cmp.companyId ");
            sb.append(" INNER JOIN t_company cmp_dl on a.company_id=cmp_dl.companyId ");
            sb.append(" where a.deleted = 0 and (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));
            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }


            //处理跳转链接
            switch (queryType) {
                case "1"://项目总数

                    break;
                case "2"://在建项目
                    sb.append("  and a.project_status in ('04','05','06') ");
                    break;
                case "3"://本月新增数
                    sb.append("  and a.createTime>date_sub(now(), INTERVAL 30 DAY) ");
                    break;
                case "4"://本月回款项目数
                    sb.append("  and a.is_payment='1' ");
                    break;
                case "5"://验收未回款项目
                    sb.append("  and a.is_payment='0' and a.project_status='07' ");
                    break;
                case "6"://已延期项目
                    sb.append("  and c.end_time<now()  and a.project_status<'07' ");
                    break;
                case "7"://已完成项目
                    sb.append("  and a.project_status in ('07')  ");
                    break;
                case "8"://应开始未开始项目
                    sb.append("  and (a.planned_end_time<now() and a.project_status='01'  or a.project_status='02' and c.end_time<now())  ");
                    break;
                case "9"://已超期项目
                    sb.append("  and c.end_time<now()  and a.project_status<'07'  ");
                    break;
                case "10"://本月已完成项目
                    sb.append(" and rec.createTime>date_sub(now(), INTERVAL 30 DAY) ");
                    break;
                default:
                    break;
            }
            //项目名称
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_name"))) {
                sb.append("  and a.project_name like  '%").append(req.getReqData().get("project_name")).append("%' ");
            }

            //项目类型
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_type"))) {
                sb.append("  and a.project_type =  '").append(req.getReqData().get("project_type")).append("' ");
            }


            //时间
            if (!StringUtils.isEmpty((String) req.getReqData().get("start_date"))) {
                sb.append("  and a.createTime >=  '").append(req.getReqData().get("start_date")).append("' ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("end_date"))) {
                sb.append("  and a.createTime <=  '").append(req.getReqData().get("end_date")).append(" 23:59:59' ");
            }

            //项目状态
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_status"))) {
                sb.append("  and a.project_status =  '").append(req.getReqData().get("project_status")).append("' ");
            }
            if (StrUtil.isNotBlank(opportunityId)) {
                sb.append(" and a.opportunity_uuid = '").append(opportunityId).append("'");
            }
            if (StrUtil.isNotBlank(customerId)) {
                sb.append(" and a.customer_uuid = '").append(customerId).append("'");
            }

            log.info("{}", sb.toString());
            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{req.getUinfo().getCompanyId()});
            sb.append(" order by  a.createTime desc ");
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});


            for (int i = 0; i < list.size(); i++) {
                String images = (String) list.get(i).get("images");
                if (!StringUtils.isEmpty(images)) {
                    String[] imageList = images.split(";", -1);
                    List<String> imgURL = new ArrayList<String>();
                    for (int k = 0; k < imageList.length; k++) {
                        String str = imageList[k];
                        imgURL.add(OssUtil.getImgSignURL(str, 0));

                    }
                    list.get(i).put("imageList", imgURL);
                }
            }

            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getSchedulingList(Connection conn, DataReq req) {
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        try {
            if (StringUtils.isEmpty((String) req.getReqData().get("start_date"))) {
                req.getReqData().put("start_date", DBUtils.getDate(0));
            }

            if (StringUtils.isEmpty((String) req.getReqData().get("end_date"))) {
                req.getReqData().put("end_date", DBUtils.getDate(+2));
            }

            //施工中项目列表
            sb.delete(0, sb.length());
            sb.append("SELECT 	a.uuid,a.project_id project_no,a.charge,a.id project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name,m.name project_type_name,n.name service_mode_name,x.name project_level_name   from 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  ");
            sb.append(" INNER JOIN t_param m on a.project_type=m.`value` and m.param_type='92'  ");
            sb.append(" INNER JOIN t_param n on a.service_mode=n.`value` and n.param_type='91'  ");
            sb.append(" INNER JOIN t_param x on a.project_level=x.`value` and x.param_type='93'  ");
            sb.append(" INNER JOIN t_project_scheduling c on a.uuid=c.project_uuid where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));
            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }

            //项目名称
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_name"))) {
                sb.append("  and a.project_name like  '%").append(req.getReqData().get("project_name")).append("%' ");
            }

            //项目类型
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_type"))) {
                sb.append("  and a.project_type =  '").append(req.getReqData().get("project_type")).append("' ");
            }


            //时间
            if (!StringUtils.isEmpty((String) req.getReqData().get("start_time"))) {
                sb.append("  and c.start_time >=  '").append(req.getReqData().get("start_time")).append("'  ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("end_time"))) {
                sb.append("  and c.end_time <=  '")
                        .append(DBUtils.getDate(DBUtils.StrToDate((String) req.getReqData().get("end_time")), 1))
                        .append("' ");
            }

            //项目状态
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_status"))) {
                sb.append("  and a.project_status =  '").append(req.getReqData().get("project_status")).append("' ");
            }

            //项目状态
            if (!StringUtils.isEmpty((String) req.getReqData().get("department_id"))) {
                sb.append("  and c.department_id =  '").append(req.getReqData().get("department_id")).append("' ");
            }

            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{req.getUinfo().getCompanyId()});
            sb.append(" order by  a.createTime desc ");
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});

            List<Map<String, Object>> row = new ArrayList<Map<String, Object>>();

            String sTime = (String) req.getReqData().get("start_date");
            String eTime = (String) req.getReqData().get("end_date");


            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> e = new LinkedHashMap<String, Object>();
                e.put("label", list.get(i).get("project_name"));
                e.put("uuid", list.get(i).get("uuid"));
                Map<String, Object> e1 = new LinkedHashMap<String, Object>();
                Map<String, Object> e2 = new LinkedHashMap<String, Object>();
                e1.put("myStart", list.get(i).get("start_time"));
                e1.put("myEnd", list.get(i).get("end_time"));
                e1.put("label", list.get(i).get("project_name"));

                e2.put("immobile", true);
                e2.put("handles", false);
                e2.put("color", "#ffffff");
                e2.put("backgroundColor", "#6699ff");
                e2.put("borderRadius", 0);
                e1.put("ganttBarConfig", e2);

                List<Object> e1_list = new ArrayList<Object>();
                e1_list.add(e1);
                e.put("bars", e1_list);
                row.add(e);

            }

            Map<String, Object> extra = new LinkedHashMap<String, Object>();
            List<String> datelist = new ArrayList<String>();
            datelist.add("");
            for (int i = 0; i < 50; i++) {
                String tmp = DBUtils.getDate(DBUtils.StrToDate(sTime), i);
                if (tmp.compareToIgnoreCase(eTime) <= 0) {
                    datelist.add(tmp);
                } else {
                    break;
                }

            }

            extra.put("rows", row);

            extra.put("list", datelist);
            extra.put("myChartStart", sTime);
            extra.put("myChartEnd", DBUtils.getDate(DBUtils.StrToDate(eTime), 1));

            res.setExtData(extra);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    //获取待分配，已分配工单列表
    public PageData getAllocatedList(Connection conn, DataReq req) {
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        try {

            //施工中项目列表
            sb.delete(0, sb.length());
            sb.append("SELECT 	a.contact,a.contact_phone,d.work_order_id,a.uuid,a.project_id project_no,a.project_address,a.charge,a.id project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name,m.name project_type_name,n.name service_mode_name,x.name project_level_name,date_format(d.createTime,'%Y-%m-%d %T')  workorder_time,f.NAME workorder_status,g.username maintain_name,soi.id as saleOrderId  from t_project a ");
            sb.append(" INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  ");
            sb.append(" INNER JOIN t_param m on a.project_type=m.`value` and m.param_type='92'  ");
            sb.append(" INNER JOIN t_param n on a.service_mode=n.`value` and n.param_type='91'  ");
            sb.append(" INNER JOIN t_param x on a.project_level=x.`value` and x.param_type='93'  ");
            sb.append(" INNER JOIN t_work_order_info  d ON d.dd_id = a.uuid  ");
            sb.append(" INNER JOIN t_param f on  d.`status` = f.`value`  AND f.param_type = '94'  ");
            sb.append(" LEFT JOIN t_user g on  g.id = d.maintain_id  ");
            sb.append(" INNER JOIN t_project_scheduling c on a.uuid=c.project_uuid");
            sb.append(" LEFT JOIN sale_order_info soi on soi.business_id = a.opportunity_uuid and soi.commit = true ");
            sb.append(" where (a.company_id like concat(?,'%') or a.manufacture_id=' ")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));
            //排期之后的项目
            sb.append("  and a.project_status =  '02' ");
            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }

            //项目名称
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_name"))) {
                sb.append("  and a.project_name like  '%").append(req.getReqData().get("project_name")).append("%' ");
            }

            //项目类型
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_type"))) {
                sb.append("  and a.project_type =  '").append(req.getReqData().get("project_type")).append("' ");
            }

            //时间
            if (!StringUtils.isEmpty((String) req.getReqData().get("start_time"))) {
                sb.append("  and c.start_time <=  '").append(req.getReqData().get("start_time")).append(" 23:59:59'");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("end_time"))) {
                sb.append("  and c.end_time >=  '").append(req.getReqData().get("end_time")).append("' ");
            }

            //项目状态
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_status"))) {
                sb.append("  and a.project_status =  '").append(req.getReqData().get("project_status")).append("' ");
            }

            //项目状态
            if (!StringUtils.isEmpty((String) req.getReqData().get("department_id"))) {
                sb.append("  and c.department_id =  '").append(req.getReqData().get("department_id")).append("' ");
            }
            System.out.println(sb.toString());
            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{req.getUinfo().getCompanyId()});
            sb.append(" order by  a.createTime desc ");
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getInstallUsers(Connection conn, DataReq req) {
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();
        try {
            String sTime = (String) req.getReqData().get("start_date");
            String eTime = (String) req.getReqData().get("end_date");
            if (StringUtils.isEmpty(sTime)) {
                sTime = DBUtils.getDate(0);
                eTime = DBUtils.getDate(3);
            }
            String conInstallString = (String) req.getReqData().get("conInstall");
            boolean conInstall = "true".equals(conInstallString);

            sb.append("SELECT a.id,a.uuid,a.nickName,min(date_format(b.planned_start_time,'%Y-%m-%d %T')) start_date,max(date_format(b.planned_end_time,'%Y-%m-%d %T')) end_date,count(b.work_order_id) cnt from t_user a ");
            sb.append(" LEFT JOIN t_work_order_info b on a.id=b.maintain_id  and b.status in ('02','04','05') "); //接单，拒单，到达3个状态
            sb.append("  where a.sfsc=0 ");
            if (conInstall) {
                sb.append("and ( a.userType='02' or a.userType='08')");
            } else {
                sb.append("and ( a.userType='01' or a.userType='02' or a.userType='08') ");
            }
            sb.append("  and a.companyId = ? ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.areacode", new String[]{"a.createUid", req.getUinfo().getId()}));

            //部门ID
            if (!StringUtils.isEmpty((String) req.getReqData().get("departmentId"))) {
                sb.append("  and a.departmentId =  '").append(req.getReqData().get("departmentId")).append("' ");
            }

            sb.append("  group by a.id,\r\n" + "	a.uuid,\r\n" + "	a.nickName order by  a.nickName desc ");
            List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});


            //格式转化
            List<Map<String, Object>> row = new ArrayList<Map<String, Object>>();


            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> e = new LinkedHashMap<String, Object>();
                if (StringUtils.isEmpty((String) list.get(i).get("start_date"))) {
                    list.get(i).put("start_date", DBUtils.getDate(0));
                }
                if (StringUtils.isEmpty((String) list.get(i).get("end_date"))) {
                    list.get(i).put("end_date", DBUtils.getDate(0));
                }


                e.put("label", list.get(i).get("nickName") + "(" + list.get(i).get("cnt") + ")");
                e.put("id", list.get(i).get("id"));
                Map<String, Object> e1 = new LinkedHashMap<String, Object>();
                Map<String, Object> e2 = new LinkedHashMap<String, Object>();
                e1.put("myStart", list.get(i).get("start_date"));
                e1.put("myEnd", list.get(i).get("end_date"));
                e1.put("label", list.get(i).get("nickName"));


                e2.put("immobile", true);
                e2.put("handles", false);
                e2.put("color", "#ffffff");
                e2.put("backgroundColor", "#6699ff");
                e2.put("borderRadius", 0);
                e1.put("ganttBarConfig", e2);

                List<Object> e1_list = new ArrayList<Object>();
                e1_list.add(e1);
                e.put("bars", e1_list);
                row.add(e);

            }

            Map<String, Object> extra = new LinkedHashMap<String, Object>();
            List<String> datelist = new ArrayList<String>();
            datelist.add("");
            for (int i = 0; i < 50; i++) {
                String tmp = DBUtils.getDate(DBUtils.StrToDate(sTime), i);
                if (tmp.compareToIgnoreCase(eTime) <= 0) {
                    datelist.add(tmp);
                } else {
                    break;
                }

            }

            extra.put("rows", row);
            extra.put("list", datelist);
            extra.put("myChartStart", sTime);
            extra.put("myChartEnd", DBUtils.getDate(DBUtils.StrToDate(eTime), 1));

            res.setPage(list);
            res.setExtData(extra);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData addWorkOrder(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        param.put("tableName", "t_work_order_info");
        param.put("company_id", req.getUinfo().getCompanyId());
        param.put("status", "02"); //待接单
        param.put("work_type_id", "1");

        try {
            String uuid = (String) param.get("uuid");
            if (StringUtils.isEmpty(uuid)) {
                param.put("work_order_id", DBUtils.getDjbh(req.getUinfo().getCompanyId(), "WOR"));
                res = insertTableData(conn, req);
                param.put("addData", "1");
            } else {
                param.put("keys", "uuid");
                res = updateTableData(conn, req);
                param.put("addData", "0");
            }

            if (res.getRetCode() == 0) {
                //插入记录表
                param.put("describe", "安装已派单"); //待接单
                res = addWorkOrderRecord(conn, req);
                if (res.getRetCode() == 0) {
                    res.setRetCode(0);
                    res.setRetMessage("OK");
                    res.setKey((String) param.get("uuid"));
                }

            }
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData addWorkOrderRecord(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        param.put("record_id", DBUtils.getDjbh(req.getUinfo().getCompanyId(), "REC"));
        param.put("tableName", "t_work_order_record");
        try {
            if ("0".equals(param.get("addData"))) {
                //res = updateTableData(conn, req) ;
                res.setRetCode(0);
            } else {
                res = insertTableData(conn, req);
            }

        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }


    public PageData changeOrderStatus(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        param.put("tableName", "t_work_order_info");
        param.put("company_id", req.getUinfo().getCompanyId());
        String woder_status = (String) param.get("status");
        String planned_start_time = (String) param.get("planned_start_time");

        try {
            param.put("keys", "work_order_id");
            if ("02".equals(woder_status)) { //如果是派单,将计划时间改为当前时间
                if (StringUtils.isEmpty(planned_start_time)) {
                    param.put("planned_start_time", DBUtils.getDate(0));
                    param.put("planned_end_time", DBUtils.getDate(1));
                }

            }

            res = updateTableData(conn, req);

            if (res.getRetCode() == 0) {
                insertProjectLog(conn, req, (String) param.get("work_order_id"), "2", "工单状态变更", "更新了工单状态");
                //插入记录表
                res = addWorkOrderRecord(conn, req);
                if (res.getRetCode() == 0) {
                    //更改项目表状态


                    String project_status = "00";
                    String project_status_name = "";
                    if ("02".equals(woder_status)) { //如果工单状态是派单，那么项目状态改为待施工
                        project_status = "03";
                        project_status_name = "待施工";
                    }

                    if ("05".equals(woder_status)) { //如果工单状态为已到达，将项目状态改为施工中
                        project_status = "04";
                        project_status_name = "施工中";
                    }

                    if ("03".equals(woder_status)) { //如果工单状态为拒单，将工单状态改为待分配
                        project_status = "02";
                        project_status_name = "待分配";
                    }

                    if ("06".equals(woder_status)) { //如果工单状态为拒单，将工单状态改为待分配
                        project_status = "06";
                        project_status_name = "待验收";
                    }

                    if (!"00".equals(project_status)) {
                        LinkedHashMap<String, Object> e = new LinkedHashMap<String, Object>();
                        e.put("tableName", "t_work_order_info");
                        e.put("keys", "work_order_id");
                        e.put("work_order_id", param.get("work_order_id"));
                        PageData resTmp = getDetailsByKey(conn, e);
                        if (resTmp.getRetCode() != 0) {
                            return resTmp;
                        }

                        updateProjectStatus(conn, (String) resTmp.getData().get("dd_id"), project_status);
                        //插入项目记录表
                        e = new LinkedHashMap<String, Object>();
                        e.put("tableName", "t_project_record");
                        e.put("project_uuid", (String) resTmp.getData().get("dd_id"));
                        e.put("project_status", project_status);
                        e.put("remark", project_status_name);

                        DataReq reqLog = new DataReq();
                        reqLog.setReqData(e);
                        reqLog.setUinfo(req.getUinfo());
                        resTmp = insertTableData(conn, reqLog);
                        if (resTmp.getRetCode() != 0) {
                            return resTmp;
                        }
                    }

                    //工单完成，插入设备信息
                    String isOUT = (String) param.get("isOUT");
                    if ("06".equals(woder_status) && !"1".equals(isOUT)) {
                        res = addOpeDev(conn, req);
                        if (res.getRetCode() != 0) {

                            return res;
                        }

                    }

                    res.setRetCode(0);
                    res.setRetMessage("OK");
                }
            }
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {
        }
        return res;
    }

    public PageData getWorkOrderList(Connection conn, DataReq req) {
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        try {
            //施工中项目列表
            sb.delete(0, sb.length());
            sb.append("SELECT '1' style,	a.images,d.`status` workorder_status_code ,d.auditStatus,d.auditComments,d.auditUserId,u1.nickName auditUserName, date_format(d.auditTime,'%Y-%m-%d %T') auditTime,a.id,a.project_address,d.work_order_id,a.uuid,a.project_id project_no,a.charge,a.id project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(d.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name,m.name project_type_name,n.name service_mode_name,x.name project_level_name,date_format(d.createTime,'%Y-%m-%d %T')  workorder_time,f.NAME workorder_status,g.nickName maintain_name,g.phone userphone,g.longitude user_longitude,g.latitude user_latitude,if(d.contact is null ,a.contact,d.contact) contact,if(d.contact_phone is null ,a.contact_phone,d.contact_phone) contact_phone,a.investment_scale,a.uuid project_uuid,a.area_code, REPLACE(xzqh.xzqhqc,'->','') xzqhqc  from 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  ");
            sb.append(" INNER JOIN t_param m on a.project_type=m.`value` and m.param_type='92'  ");
            sb.append(" INNER JOIN t_param n on a.service_mode=n.`value` and n.param_type='91'  ");
            sb.append(" INNER JOIN t_param x on a.project_level=x.`value` and x.param_type='93'  ");
            sb.append(" LEFT JOIN t_work_order_info  d ON d.dd_id = a.uuid  ");
            sb.append(" LEFT JOIN t_param f on  d.`status` = f.`value`  AND f.param_type = '94'  ");
            sb.append(" LEFT JOIN t_user g on  g.id = d.maintain_id  ");
            sb.append(" LEFT JOIN t_project_scheduling c on a.uuid=c.project_uuid ");
            sb.append(" LEFT JOIN t_user u1 on  u1.id = d.auditUserId  ");
            sb.append(" LEFT JOIN t_xzqh xzqh on  xzqh.xzqhdm = a.area_code  ");
            sb.append(" where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  ");
            sb.append(" and a.deleted = 0");
            if ("01".equals(req.getUinfo().getUserType())) { //如果是安装人员，那么看自己的单据
                sb.append("  and (d.maintain_id =")
                        .append(req.getUinfo().getId())
                        .append(" or a.createUid=")
                        .append(req.getUinfo().getId())
                        .append(")");
            }

            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));

            if (!StringUtils.isEmpty((String) req.getReqData().get("project_uuid"))) {
                sb.append("  and a.uuid =  '").append(req.getReqData().get("project_uuid")).append("' ");
            }

            //安装人员
            if (!StringUtils.isEmpty((String) req.getReqData().get("maintain_id"))) {
                sb.append("  and d.maintain_id =  '").append(req.getReqData().get("maintain_id")).append("' ");
            }


            //进行中的工单
            if (!StringUtils.isEmpty((String) req.getReqData().get("in_progress"))) {
                sb.append("  and d.`status` <  '06' and d.`status` >  '01' ");  // 已分配未完成的工单

            }


            //进行中的工单
            if (!StringUtils.isEmpty((String) req.getReqData().get("user_progress"))) {
                String user_progress = (String) req.getReqData().get("user_progress");
                switch (user_progress) {
                    case "0":
                        sb.append("  and d.`status` in ('01','02') ");
                        break;
                    case "1":
                        sb.append("  and d.`status` in ('03','04','05') ");
                        break;
                    case "2":
                        sb.append("  and d.`status` in ('06','07','08','09','10') ");
                        break;
                    default:
                        break;
                }


            }


            //审核状态
            if (!StringUtils.isEmpty((String) req.getReqData().get("auditStatus"))) {
                sb.append("  and d.auditStatus =  '").append(req.getReqData().get("auditStatus")).append("' ");
                sb.append("  and (a.project_status =  '06' or a.project_status =  '07')");
            }


            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }

            //项目名称
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_name"))) {
                sb.append("  and a.project_name like  '%").append(req.getReqData().get("project_name")).append("%' ");
            }

            //项目类型
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_type"))) {
                sb.append("  and a.project_type =  '").append(req.getReqData().get("project_type")).append("' ");
            }


            //时间
            // 修改为以t_project的计划时间为查询条件
            if (!StringUtils.isEmpty((String) req.getReqData().get("start_date"))) {
//                sb.append("  and d.createTime >=  '").append(req.getReqData().get("start_date")).append("'");
                sb.append("  and a.planned_start_time >=  '").append(req.getReqData().get("start_date")).append("'");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("end_date"))) {
//                sb.append("  and d.createTime <=  '").append(req.getReqData().get("end_date")).append(" 23:59:59' ");
                sb.append("  and a.planned_end_time <=  '").append(req.getReqData().get("end_date")).append(" 23:59:59' ");
            }

            //项目状态
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_status"))) {
                sb.append("  and a.project_status =  '").append(req.getReqData().get("project_status")).append("' ");
            }

            //项目状态
            if (!StringUtils.isEmpty((String) req.getReqData().get("workorder_status"))) {
                sb.append("  and d.status =  '").append(req.getReqData().get("workorder_status")).append("' ");
            }

            //项目状态
            if (!StringUtils.isEmpty((String) req.getReqData().get("department_id"))) {
                sb.append("  and c.department_id =  '").append(req.getReqData().get("department_id")).append("' ");
            }

            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{req.getUinfo().getCompanyId()});


            sb.append(" order by  a.createTime desc ");
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            for (int i = 0; i < list.size(); i++) {
                BigDecimal latitude = (BigDecimal) list.get(i).get("latitude");
                BigDecimal longitude = (BigDecimal) list.get(i).get("longitude");
                BigDecimal user_longitude = (BigDecimal) list.get(i).get("user_longitude");
                BigDecimal user_latitud = (BigDecimal) list.get(i).get("user_latitude");

                List<BigDecimal> lnglat = new ArrayList<BigDecimal>();
                lnglat.add(longitude);
                lnglat.add(latitude);
                list.get(i).put("lnglat", lnglat);

                lnglat = new ArrayList<BigDecimal>();
                lnglat.add(user_longitude);
                lnglat.add(user_latitud);
                list.get(i).put("user_lnglat", lnglat);

                String images = (String) list.get(i).get("images");
                if (!StringUtils.isEmpty(images)) {
                    String[] imageList = images.split(";", -1);
                    List<String> imgURL = new ArrayList<String>();
                    for (int k = 0; k < imageList.length; k++) {
                        String str = imageList[k];
                        imgURL.add(OssUtil.getImgSignURL(str, 0));
                    }
                    list.get(i).put("imageList", imgURL);
                }
            }
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }


    public PageData getWorkOrderRecordList(Connection conn, DataReq req) {
        PageData res = new PageData();
        try {
            String work_order_id = (String) req.getReqData().get("work_order_id");
            List<Map<String, Object>> list = getWorkOrderRecordList(conn, work_order_id);
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }


    public List<Map<String, Object>> getWorkOrderRecordList(Connection conn, String work_order_id) {
        StringBuffer sb = new StringBuffer();
        List<Map<String, Object>> list = null;
        try {

            sb.append(" SELECT a.work_order_id,a.record_id,`status`,b.`name` status_name,date_format(a.createTime,'%Y-%m-%d %T') createTime,a.createUid,c.nickName,a.images from t_work_order_record a INNER JOIN t_param b on a.`status`=b.`value` and b.param_type='94' LEFT JOIN t_user c on a.createUid=c.id ");
            sb.append(" where a.work_order_id=? ");
            sb.append(" ORDER BY a.id desc ");
            list = queryForList(conn, sb.toString(), new Object[]{work_order_id});

            for (int i = 0; i < list.size(); i++) {
                String images = (String) list.get(i).get("images");
                if (!StringUtils.isEmpty(images)) {
                    String[] imageList = images.split(";", -1);
                    List<String> imgURL = new ArrayList<String>();
                    for (int k = 0; k < imageList.length; k++) {
                        String str = imageList[k];
                        imgURL.add(OssUtil.getImgSignURL(str, 0));

                    }
                    list.get(i).put("imageList", imgURL);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return list;
    }

    public PageData addOpeDev(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        Connection conn_ope = null;
        try {
            conn_ope = DBUtils.getDBConn_Ope();
            String wx_id = (String) param.get("wx_id");
            String sblx_id = (String) param.get("sblx_id");
            String sbxlh = (String) param.get("sbxlh");
            String zbrq = (String) param.get("zbrq");
            String project_uuid = (String) param.get("project_uuid");
            if (StringUtils.isEmpty(project_uuid)) {
                res.setRetCode(9);
                res.setRetMessage("项目uuid不能为空！");
                return res;
            }

            if (StringUtils.isEmpty(wx_id)) {
                res.setRetCode(9);
                res.setRetMessage("项目传输模块编号不能为空！");
                return res;
            }

            if (StringUtils.isEmpty(sblx_id)) {
                res.setRetCode(9);
                res.setRetMessage("项目型号ID不能为空！");
                return res;
            }

            if (StringUtils.isEmpty(sbxlh)) {
                res.setRetCode(9);
                res.setRetMessage("设备序列号不能为空！");
                return res;
            }

            if (StringUtils.isEmpty(zbrq)) {
                res.setRetCode(9);
                res.setRetMessage("设备质保日期不能为空！");
                return res;
            }


            String jd = (String) param.get("longitude");
            String wd = (String) param.get("latitude");
            String xmlxr = (String) param.get("contact");
            String xmlxrdh = (String) param.get("contact_phone");
            String xmdz = (String) param.get("project_address");
            String area_code = (String) param.get("area_code");

            String manufacture_id_ope = (String) param.get("manufacture_id");


            //根据工单编号获取项目信息
            PageData resTmp = getProjectInfo(conn, project_uuid);
            if (resTmp.getRetCode() == 0) {
                if (StringUtils.isEmpty(xmlxr)) {
                    xmlxr = (String) resTmp.getData().get("contact");
                }
                if (StringUtils.isEmpty(xmlxrdh)) {
                    xmlxrdh = (String) resTmp.getData().get("contact_phone");
                }
                if (StringUtils.isEmpty(xmdz)) {
                    xmdz = (String) resTmp.getData().get("project_address");
                }
                if (StringUtils.isEmpty(area_code)) {
                    area_code = (String) resTmp.getData().get("area_code");
                }
                if (StringUtils.isEmpty(jd)) {
                    jd = (String) resTmp.getData().get("longitude");
                }
                if (StringUtils.isEmpty(wd)) {
                    wd = (String) resTmp.getData().get("latitude");
                }
            }


            if (StringUtils.isEmpty(xmlxr)) {
                res.setRetCode(9);
                res.setRetMessage("项目联系人不能为空！");
                return res;
            }

            if (StringUtils.isEmpty(xmlxrdh)) {
                res.setRetCode(9);
                res.setRetMessage("项目联系人电话不能为空！");
                return res;
            }

            if (StringUtils.isEmpty(xmdz)) {
                res.setRetCode(9);
                res.setRetMessage("项目地址不能为空！");
                return res;
            }

            if (StringUtils.isEmpty(area_code)) {
                res.setRetCode(9);
                res.setRetMessage("项目所属区域不能为空！");
                return res;
            }

            if (StringUtils.isEmpty(jd)) {
                res.setRetCode(9);
                res.setRetMessage("项目经度不能为空！");
                return res;
            }

            if (StringUtils.isEmpty(wd)) {
                res.setRetCode(9);
                res.setRetMessage("项目纬度不能为空！");
                return res;
            }


            String sfdls = "0";
            String agent_id = "";
            if ("02".equals(req.getUinfo().getCompanyType())) {
                sfdls = "1";
                agent_id = req.getUinfo().getCompanyId();
            }


            //根据wxID查询厂商ID，协议ID
            resTmp = getWxInfo(conn_ope, wx_id, manufacture_id_ope);
            if (resTmp.getRetCode() != 0) {
                return resTmp;
            }


            String manufacture_id = (String) resTmp.getData().get("manufacture_id");
            String protocol_id = (String) resTmp.getData().get("protocol_id");

            //生成客户
            Map<String, String> e = new LinkedHashMap<String, String>();
            String kh_id = DBUtils.getDjbh(req.getUinfo().getCompanyId(), "KH_");
            e.put("kh_id", kh_id);
            e.put("manufacture_id", manufacture_id); //待修改
            e.put("khmc", xmlxr);
            e.put("khjc", xmlxr);
            e.put("khlxr", xmlxr);
            e.put("lxdh", xmlxrdh);
            e.put("ssqy", area_code);
            e.put("sfdls", sfdls);
            e.put("agent_id", agent_id);
            e.put("sjly", "agent");
            int cnt = insertData(conn_ope, "is_client_info", e);
            if (cnt <= 0) {
                conn_ope.rollback();
                res.setRetCode(9);
                res.setRetMessage("is_client_info插入失败");
                return res;
            }

            //生成合同
            e = new LinkedHashMap<String, String>();
            String htbh_id = DBUtils.getDjbh(req.getUinfo().getCompanyId(), "HT_");
            e.put("htbh_id", htbh_id);
            e.put("manufacture_id", manufacture_id); //待修改
            e.put("htlx", "htlx-01");
            e.put("htbh", htbh_id);
            e.put("xsy_id", "000");
            e.put("dhf_id", kh_id);
            e.put("xssl", "1");
            e.put("qdrq", DBUtils.getCurTime());
            cnt = insertData(conn_ope, "is_contract_info", e);
            if (cnt <= 0) {
                conn_ope.rollback();
                res.setRetCode(9);
                res.setRetMessage("is_contract_info插入失败");
                return res;
            }

            //生成Install_Info
            e = new LinkedHashMap<String, String>();
            String dd_id = project_uuid; //DBUtils.getDjbh(req.getUinfo().getCompanyId(), "DD_") ;
            e.put("dd_id", dd_id);
            e.put("manufacture_id", manufacture_id);
            e.put("kh_id", kh_id);
            e.put("htbh", htbh_id);
            e.put("xmdz", xmdz);
            e.put("xmlxr", xmlxr);
            e.put("xmlxrdh", xmlxrdh);
            e.put("xzqh", area_code);
            e.put("xmmc", xmdz);
            e.put("xmjc", xmdz);
            e.put("xmbh", "000");
            e.put("jd", jd);
            e.put("wd", wd);
            e.put("sjly", "agent");
            e.put("scddh", project_uuid);

            cnt = insertData(conn_ope, "is_install_info", e);
            if (cnt <= 0) {
                conn_ope.rollback();
                res.setRetCode(9);
                res.setRetMessage("is_install_info插入失败");
                return res;
            }

            //生成设备表
            e = new LinkedHashMap<String, String>();
//            String sb_id = DBUtils.getDjbh(req.getUinfo().getCompanyId(), "SB_");
            String sb_id = "SB_" + IdUtils.getIncreaseIdByNanoTime();
            e.put("sb_id", sb_id);
            e.put("dd_id", dd_id);
            e.put("manufacture_id", manufacture_id);
            e.put("htbh", htbh_id);
            e.put("protocol_id", protocol_id);
            e.put("htbh", htbh_id);
            e.put("sblx_id", sblx_id);
            e.put("sbxlh", sbxlh);
            e.put("jd", jd);
            e.put("wd", wd);
            e.put("sjly", "agent");
            e.put("zbq", zbrq);
            e.put("wx_id", wx_id);
            e.put("agent_id", agent_id);

            e.put("sjly", "agent");
            cnt = insertData(conn_ope, "is_equipment_info", e);
            if (cnt <= 0) {
                conn_ope.rollback();
                res.setRetCode(9);
                res.setRetMessage("is_equipment_info插入失败");
                return res;
            }

            conn_ope.commit();
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
            conn_ope.rollback();
        } finally {
            if (conn_ope != null) {
                try {
                    conn_ope.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    public int insertData(Connection conn, String tblname, Map<String, String> e) throws SQLException {
        StringBuffer sb_field = new StringBuffer();
        StringBuffer sb_value = new StringBuffer();
        List<String> listData = new ArrayList<String>();
        for (Map.Entry<String, String> entry : e.entrySet()) {
            sb_field.append("`").append(entry.getKey()).append("`,");
            sb_value.append("?").append(",");
            listData.add(entry.getValue());
        }
        sb_field.deleteCharAt(sb_field.length() - 1);
        sb_value.deleteCharAt(sb_value.length() - 1);
        sb_field.insert(0, "(").insert(0, "`" + tblname + "`").insert(0, "insert into ");
        sb_field.append(")values(").append(sb_value).append(")");

        int cnt = update(conn, sb_field.toString(), listData.toArray());
        return cnt;
    }


    public PageData getOpportunityList(Connection conn, DataReq req) {
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();

        try {
            sb.append(" SELECT a.uuid opportunity_uuid, a.opportunitystage,a.opportunitytype,a.opportunityname,a.customerId customer_uuid,a.clueId clue_uuid,a.opportunityowner,a.xzqhdm,a.projectaddress,a.projectarea,a.longitude,a.latitude,a.personincharge,a.opportunitymoney   from opportunity a  ");
            sb.append(" where  not EXISTS (SELECT 1 from t_project b where a.uuid=b.contract_no) ");
            //sb.append(" and a.opportunitystage = '05' ") ;
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.xzqhdm", new String[]{"a.createUid", req.getUinfo().getId()}));


            //项目名称
            if (!StringUtils.isEmpty((String) req.getReqData().get("opportunityname"))) {
                sb.append("  and a.opportunityname like  '%")
                        .append(req.getReqData().get("opportunityname"))
                        .append("%' ");
            }

            //时间
            if (!StringUtils.isEmpty((String) req.getReqData().get("start_date"))) {
                sb.append("  and d.createTime >=  '").append(req.getReqData().get("start_date")).append("'");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("end_date"))) {
                sb.append("  and d.createTime <=  '").append(req.getReqData().get("end_date")).append(" 23:59:59' ");
            }

            sb.append(" order by  a.createTime desc limit 100 ");
            List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{});

            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getProjectCheckList(Connection conn, DataReq req) {
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        try {

            //施工中项目列表
            sb.delete(0, sb.length());
            sb.append("SELECT 	a.id,a.project_address,a.uuid project_uuid,a.project_id project_no,a.charge,a.project_name,a.project_status,date_format(a.createTime,'%Y-%m-%d %T') createTime,b.name project_status_name,m.name project_type_name,n.name service_mode_name,x.name project_level_name,g.nickname maintain_name,pc.check_no,pc.uuid check_uuid,pc.check_type,pc.check_user_id,pc.check_content,pc.check_results,pc.images,pc.check_status,date_format(pc.checkTime,'%Y-%m-%d %T') checkTime  from 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  ");
            sb.append(" INNER JOIN t_param m on a.project_type=m.`value` and m.param_type='92'  ");
            sb.append(" INNER JOIN t_param n on a.service_mode=n.`value` and n.param_type='91'  ");
            sb.append(" INNER JOIN t_param x on a.project_level=x.`value` and x.param_type='93'  ");
            sb.append(" INNER JOIN t_project_check pc on  a.uuid = pc.project_uuid  ");
            sb.append(" INNER JOIN t_user g on  g.id = pc.check_user_id  ");
            sb.append(" LEFT JOIN t_project_scheduling c on a.uuid=c.project_uuid where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  ");


            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));


            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }

            //检查UUID
            if (!StringUtils.isEmpty((String) req.getReqData().get("uuid"))) {
                sb.append("  and pc.uuid =  '").append(req.getReqData().get("uuid")).append("' ");
            }


            //项目UUID
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_uuid"))) {
                sb.append("  and a.uuid =  '").append(req.getReqData().get("project_uuid")).append("' ");
            }

            //项目名称
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_name"))) {
                sb.append("  and a.project_name like  '%").append(req.getReqData().get("project_name")).append("%' ");
            }

            //项目类型
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_type"))) {
                sb.append("  and a.project_type =  '").append(req.getReqData().get("project_type")).append("' ");
            }


            //时间
            if (!StringUtils.isEmpty((String) req.getReqData().get("start_date"))) {
                sb.append("  and pc.checkTime >=  '").append(req.getReqData().get("start_date")).append("'");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("end_date"))) {
                sb.append("  and pc.checkTime <=  '").append(req.getReqData().get("end_date")).append(" 23:59:59' ");
            }

            //项目状态
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_status"))) {
                sb.append("  and a.project_status =  '").append(req.getReqData().get("project_status")).append("' ");
            }

            //检查状态
            if (!StringUtils.isEmpty((String) req.getReqData().get("check_status"))) {
                sb.append("  and pc.check_status =  '").append(req.getReqData().get("check_status")).append("' ");
            }

            //检查类型
            sb.append("  and pc.check_type =  '").append(req.getReqData().get("check_type")).append("' ");


            if (!StringUtils.isEmpty((String) req.getReqData().get("department_id"))) {
                sb.append("  and c.department_id =  '").append(req.getReqData().get("department_id")).append("' ");
            }

            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{req.getUinfo().getCompanyId()});


            sb.append(" order by  a.createTime desc ");
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});


            for (int i = 0; i < list.size(); i++) {
                String images = (String) list.get(i).get("images");
                if (!StringUtils.isEmpty(images)) {
                    String[] imageList = images.split(";", -1);
                    List<String> imgURL = new ArrayList<String>();
                    for (int k = 0; k < imageList.length; k++) {
                        String str = imageList[k];
                        imgURL.add(OssUtil.getImgSignURL(str, 0));

                    }
                    list.get(i).put("imageList", imgURL);
                }
            }

            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getLogList(Connection conn, DataReq req) {
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        try {

            //施工中项目列表
            sb.delete(0, sb.length());

            sb.append(" SELECT ");
            sb.append(" date_format( a.createTime, '%Y-%m-%d %T' ) createTime, ");
            sb.append(" IF ");
            sb.append(" ( a.link_type = '2', d.dd_id, a.link_id ) link_id, ");
            sb.append(" a.link_type, ");
            sb.append(" x.NAME link_type_name, ");
            sb.append(" a.title, ");
            sb.append(" b.areacode, ");
            sb.append(" b.username, ");
            sb.append(" c.project_name, ");
            sb.append(" a.remark  ");
            sb.append(" from ");
            sb.append(" t_project_log a ");
            sb.append(" INNER JOIN t_user b ON a.createUid = b.id ");
            sb.append(" INNER JOIN t_param x ON a.link_type = x.`value`  ");
            sb.append(" AND x.param_type = '84' ");
            sb.append(" LEFT JOIN t_project c ON a.link_id = c.uuid  ");
            sb.append(" AND a.link_type = '1' ");
            sb.append(" LEFT JOIN t_work_order_info d ON a.link_id = d.work_order_id  ");
            sb.append(" AND a.link_type = '2' ");
            sb.append(" LEFT JOIN t_project_check e ON a.link_id = e.project_uuid  ");
            sb.append(" AND ( a.link_type = '3' OR a.link_type = '4' ) ");
            sb.append(" LEFT JOIN t_project_bulid_log f ON a.link_id = f.uuid ");
            sb.append("  where a.link_type<>'5' and b.companyId like concat(?,'%') ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "b.areacode", new String[]{"a.createUid", req.getUinfo().getId()}));

            if (!StringUtils.isEmpty((String) req.getReqData().get("link_type"))) {
                sb.append("  and a.link_type  = '").append(req.getReqData().get("link_type")).append("' ");
            }


            //时间
            if (!StringUtils.isEmpty((String) req.getReqData().get("start_date"))) {
                sb.append("  and a.createTime >=  '").append(req.getReqData().get("start_date")).append("'");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("end_date"))) {
                sb.append("  and a.createTime <=  '").append(req.getReqData().get("end_date")).append(" 23:59:59' ");
            }

            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{req.getUinfo().getCompanyId()});


            sb.append(" order by  a.createTime desc ");
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});

            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }


    public PageData getVisualization(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();
        Map<String, Object> extData = new LinkedHashMap<String, Object>();
        try {
            //获取我的代办事项
            sb.delete(0, sb.length());
            sb.append(" select b.`value` ,b.`name`,count(a.project_status) cnt from t_param b LEFT JOIN t_project a on a.project_status=b.`value` and (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  ");

            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));


            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }

            sb.append(" where b.param_type='90'  ");

            sb.append(" GROUP BY b.`value` order BY b.`value` ");
            List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            //加总数
            BigDecimal sum = new BigDecimal("0");
            for (int i = 0; i < list.size(); i++) {
                String cnt = list.get(i).get("cnt").toString();
                sum = sum.add(new BigDecimal(cnt));
            }
            list.get(0).put("sumcnt", sum.toString());
            extData.put("topData", list);

            sb.delete(0, sb.length());
            sb.append(" SELECT");
            sb.append(" sum(if(b.`status`='01',1,0)) plan_cnt,sum(if(b.`status`='02',1,0)) dispatch_cnt from t_work_order_info a INNER JOIN t_work_order_record b on a.work_order_id=b.work_order_id ");
            sb.append(" where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("') and b.createTime>=date(now()) and (b.`status`='01' or b.`status`='02')");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.xzqhdm", new String[]{"a.createUid", req.getUinfo().getId()}));
            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            Map<String, Object> e = new LinkedHashMap<String, Object>();
            if (list.size() > 0) {

                e.put("plan_cnt", list.get(0).get("plan_cnt"));
                e.put("dispatch_cnt", list.get(0).get("dispatch_cnt"));
            } else {
                e.put("plan_cnt", "0");
                e.put("dispatch_cnt", "0");
            }
            extData.put("topData_today", e);

            //按天获取新建项目曲线图
            sb.delete(0, sb.length());

            sb.append(" SELECT a.uuid project_uuid, a.project_id,a.project_name,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T')  planned_end_time,date_format(b.start_time,'%Y-%m-%d %T') start_time,date_format(b.end_time,'%Y-%m-%d %T') end_time,a.project_status, ");
            sb.append(" if(a.project_status='01',datediff(now(),a.planned_end_time),datediff(now(),b.end_time) ) overDate ");
            sb.append(" from  t_project a left JOIN t_project_scheduling b on a.uuid=b.project_uuid ");
            sb.append(" where (a.planned_end_time<now() and a.project_status='01'  or a.project_status<'07' and b.end_time<now()) ");
            sb.append(" and (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  ");

            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));

            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }
            sb.append("  order by b.end_time,a.planned_end_time  limit 20 ");
            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            extData.put("outDateProjectLine", list);


            //工单状态数据
            sb.delete(0, sb.length());
            sb.append(" SELECT");
            sb.append(" sum( IF ( a.`status` = '01', 1, 0 ) ) status01,");
            sb.append(" sum( IF ( a.`status` = '02', 1, 0 ) ) status02, ");
            sb.append(" sum( IF ( a.`status` = '05', 1, 0 ) ) status05, ");
            sb.append(" sum( IF ( a.`status` = '06' or a.`status` = '07' or a.`status` = '08', 1, 0 ) ) status06");
            sb.append(" FROM");
            sb.append(" t_work_order_info a where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("') ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.xzqhdm", new String[]{"a.createUid", req.getUinfo().getId()}));
            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            e = new LinkedHashMap<String, Object>();
            if (list.size() > 0) {
                e.put("status01", list.get(0).get("status01"));
                e.put("status02", list.get(0).get("status02"));
                e.put("status05", list.get(0).get("status05"));
                e.put("status06", list.get(0).get("status06"));
            } else {
                e.put("status01", "0");
                e.put("status02", "0");
                e.put("status05", "0");
                e.put("status06", "0");
            }
            extData.put("workorder_status", e);

            sb.delete(0, sb.length());
            sb.append(" SELECT ");
            sb.append("  date_format(b.createTime,'%Y-%m-%d') createTime,sum(if(a.project_type='01',1,0)) civil_cnt,sum(if(a.project_type='02',1,0)) Industry_cnt ");
            sb.append(" FROM t_project a INNER JOIN  t_work_order_info b on a.uuid=b.dd_id ");
            sb.append("   where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  and a.createTime>'")
                    .append(DBUtils.getDate(-10))
                    .append("' ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));
            sb.append(" GROUP BY date_format(b.createTime,'%Y-%m-%d') ");
            sb.append(" order BY date_format(b.createTime,'%Y-%m-%d') ");

            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            extData.put("barData", list);

            //项目数
            sb.delete(0, sb.length());
            sb.append(" SELECT ");
            sb.append("   count(1) all_cnt,year(now())-2 year01_name,year(now())-1 year02_name,year(now()) year03_name, ");
            sb.append(" 	sum( IF(year(a.createTime) = year(now())-2, 1, 0 )) year01 , ");
            sb.append(" sum( IF(year(a.createTime) = year(now())-1, 1, 0 )) year02 , ");
            sb.append(" 	sum( IF(year(a.createTime) = year(now()), 1, 0 ) ) year03 ,sum( IF(b.end_time<now() and a.project_status<>'07', 1, 0 ) ) later_cnt  ");
            sb.append(" FROM ");
            sb.append(" 	t_project a ");
            sb.append(" 	LEFT JOIN t_project_scheduling b ON a.uuid = b.project_uuid   ");
            sb.append("   where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  and a.createTime>'")
                    .append(DBUtils.getDate(-10))
                    .append("' ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));

            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            extData.put("projectData", list);


            res.setExtData(extData);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getAgentManufactorList(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();
        Map<String, Object> extData = new LinkedHashMap<String, Object>();
        try {
            //获取我的代办事项
            sb.delete(0, sb.length());
            sb.append(" select b.`value` ,b.`name`,count(a.project_status) cnt from t_param b LEFT JOIN t_project a on a.project_status=b.`value` and (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')   ");

            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));


            if (!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
                sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
            }

            sb.append(" where b.param_type='90'  ");

            sb.append(" GROUP BY b.`value` order BY b.`value` ");
            List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            //加总数
            BigDecimal sum = new BigDecimal("0");
            for (int i = 0; i < list.size(); i++) {
                String cnt = list.get(i).get("cnt").toString();
                sum = sum.add(new BigDecimal(cnt));
            }
            list.get(0).put("sumcnt", sum.toString());
            extData.put("topData", list);


            sb.delete(0, sb.length());
            sb.append(" SELECT");
            sb.append(" sum(if(b.`status`='01',1,0)) plan_cnt,sum(if(b.`status`='02',1,0)) dispatch_cnt from t_work_order_info a INNER JOIN t_work_order_record b on a.work_order_id=b.work_order_id ");
            sb.append(" where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("') and b.createTime>=date(now()) and (b.`status`='01' or b.`status`='02')");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.xzqhdm", new String[]{"a.createUid", req.getUinfo().getId()}));
            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            Map<String, Object> e = new LinkedHashMap<String, Object>();
            if (list.size() > 0) {

                e.put("plan_cnt", list.get(0).get("plan_cnt"));
                e.put("dispatch_cnt", list.get(0).get("dispatch_cnt"));
            } else {
                e.put("plan_cnt", "0");
                e.put("dispatch_cnt", "0");
            }
            extData.put("topData_today", e);


            //工单状态数据
            sb.delete(0, sb.length());
            sb.append(" SELECT");
            sb.append(" sum( IF ( a.`status` = '01', 1, 0 ) ) status01,");
            sb.append(" sum( IF ( a.`status` = '02', 1, 0 ) ) status02, ");
            sb.append(" sum( IF ( a.`status` = '05', 1, 0 ) ) status05, ");
            sb.append(" sum( IF ( a.`status` = '06' or a.`status` = '07' or a.`status` = '08', 1, 0 ) ) status06");
            sb.append(" FROM");
            sb.append(" t_work_order_info a where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("') ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.xzqhdm", new String[]{"a.createUid", req.getUinfo().getId()}));
            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            e = new LinkedHashMap<String, Object>();
            if (list.size() > 0) {
                e.put("status01", list.get(0).get("status01"));
                e.put("status02", list.get(0).get("status02"));
                e.put("status05", list.get(0).get("status05"));
                e.put("status06", list.get(0).get("status06"));
            } else {
                e.put("status01", "0");
                e.put("status02", "0");
                e.put("status05", "0");
                e.put("status06", "0");
            }
            extData.put("workorder_status", e);

            sb.delete(0, sb.length());
            sb.append(" SELECT ");
            sb.append("  date_format(b.createTime,'%Y-%m-%d') createTime,sum(if(a.project_type='01',1,0)) civil_cnt,sum(if(a.project_type='02',1,0)) Industry_cnt ");
            sb.append(" FROM t_project a INNER JOIN  t_work_order_info b on a.uuid=b.dd_id ");
            sb.append("   where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  and a.createTime>'")
                    .append(DBUtils.getDate(-10))
                    .append("' ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));
            sb.append(" GROUP BY date_format(b.createTime,'%Y-%m-%d') ");
            sb.append(" order BY date_format(b.createTime,'%Y-%m-%d') ");

            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            extData.put("barData", list);

            //项目数
            sb.delete(0, sb.length());
            sb.append(" SELECT ");
            sb.append("   count(1) all_cnt,year(now())-2 year01_name,year(now())-1 year02_name,year(now()) year03_name, ");
            sb.append(" 	sum( IF(year(a.createTime) = year(now())-2, 1, 0 )) year01 , ");
            sb.append(" sum( IF(year(a.createTime) = year(now())-1, 1, 0 )) year02 , ");
            sb.append(" 	sum( IF(year(a.createTime) = year(now()), 1, 0 ) ) year03 ,sum( IF(b.end_time<now() and a.project_status<>'07', 1, 0 ) ) later_cnt  ");
            sb.append(" FROM ");
            sb.append(" 	t_project a ");
            sb.append(" 	INNER JOIN t_project_scheduling b ON a.uuid = b.project_uuid   ");
            sb.append("   where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  and a.createTime>'")
                    .append(DBUtils.getDate(-10))
                    .append("' ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));

            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            extData.put("projectData", list);


            res.setExtData(extData);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getProjectfileList(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        try {
            StringBuffer sb = new StringBuffer();
            StringBuffer sbfrom = new StringBuffer();
            //获取文件列表
            sb.append(" select d.username,date_format(a.createtime,'%Y-%m-%d %H:%i:%S') createtime,a.uuid,a.projectId,a.filetype,c.`name` filetypeName,a.filename,a.filepath,a.memo ");
            sb.append(" from project_file a ");
            sb.append(" left join t_param c on a.filetype=c.`value` and c.param_type='29' ");
            sb.append(" left join t_user d on a.createUid=d.id ");
            sb.append(" where a.isdelete=0 and projectId=? ");
            if (!StringUtils.isEmpty((String) param.get("fileName"))) {
                sb.append(" and a.filename like '%").append(param.get("fileName")).append("%' ");
            }
            if (!StringUtils.isEmpty((String) param.get("fileType"))) {
                sb.append(" and a.filetype = '").append(param.get("fileType")).append("' ");
            }
            sb.append(" order by a.createTime desc");

            sb = DBUtils.sql_check(sb);
            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{param.get("projectId")});
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> fileList = queryForList(conn, sb.toString(), new Object[]{param.get("projectId")});

            //获取客户文件真实路径
            Iterator<Map<String, Object>> iterator = fileList.iterator();
            while (iterator.hasNext()) {
                Map<String, Object> next = iterator.next();
                String filepath = (String) next.get("filepath");
                String imgSignURL = OssUtil.getFileSignURL(filepath, 0);
                next.put("realFilePath", imgSignURL);
            }

            res.setPage(fileList);

            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData saveProjectFile(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        param.put("tableName", "project_file");
        param.put("isdelete", "0");
        //添加操作日志
        param.put("OperationLogType", "02");
        param.put("OperationLogId", "updateCustomerId");
        try {

            String uuid = (String) param.get("uuid");
            if (StringUtils.isEmpty(uuid)) {
                param.put("OperationLogName", "增加项目文件");
                param.put("OperationMemo", req.getUinfo().getNickName() + "添加了一条项目文件");
                res = insertTableData(conn, req);
            } else {
                param.put("keys", "uuid");
                param.put("OperationLogName", "修改项目文件");
                param.put("OperationMemo", req.getUinfo().getNickName() + "修改了一条项目文件");
                res = updateTableData(conn, req);
            }
            if (res.getRetCode() == 0) {
                res.setRetCode(0);
                res.setRetMessage("OK");
                res.setKey((String) param.get("uuid"));
            }
        } catch (Exception e) {
            e.printStackTrace();

        } finally {
        }
        return res;
    }

    @SuppressWarnings("unchecked")
    public PageData delProjectFile(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("项目文件删除失败");
        param.put("tableName", "project_file");
        param.put("keys", "uuid");
        param.put("isdelete", "1");
        param.put("uuid", param.get("fileId"));
        try {
            PageData sqlInfo = getUpdateSQL(conn, req);
            if (sqlInfo.getRetCode() != 0) {
                return sqlInfo;
            }
            //修改主表
            StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            update(conn, sb_field.toString(), listData.toArray());

            param.put("OperationMemo", req.getUinfo().getNickName() + "删除了一条项目文件");
            SaveOperatelog(req, "02", "删除项目文件信息", sb_field + "---" + listData, conn);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }
        return res;
    }

    public PageData uploadFile(DataReq req, MultipartFile file) {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("上传文件失败");
        String fileName = "";
        String OriginalFilename = "";
        try {
            if (file != null && file.getSize() > 0) {
                String prefix = ".doc";
                OriginalFilename = file.getOriginalFilename();
                if (OriginalFilename != null) {
                    prefix = OriginalFilename.substring(OriginalFilename.lastIndexOf("."));
                }
                fileName = "file/" + DBUitl.getDate(0) + "/file_" + param.get("uuid") + "_" + String.valueOf((new java.util.Date()).getTime()) + prefix;
                boolean isOK = DBUitl.SaveImageFromStream(file.getInputStream(), fileName);
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("filePath", fileName);
                map.put("fileName", OriginalFilename);
                if (!isOK) {
                    return res;
                } else {
                    res.setRetCode(0);
                    res.setData(map);
                    res.setRetMessage("OK");
                }
            } else {
                res.setRetCode(9);
                res.setRetMessage("上传文件失败");
            }

        } catch (Exception e) {
            e.printStackTrace();
            res.setRetCode(9);
            res.setRetMessage("上传文件失败");
        } finally {

        }
        return res;
    }

    public PageData sendProjectOvertimeRemind(Connection conn, DataReq req) {
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();

        try {
            String project_uuid = (String) req.getReqData().get("project_uuid");

            sb.append(" SELECT  ");
            sb.append(" creater.phone creater_phone,  ");
            sb.append(" workorder.phone workorder_phone,  ");
            sb.append(" scheduling.phone scheduling_phone,a.project_name, date_format(b.end_time,'%Y-%m-%d %T') end_time ");
            sb.append(" FROM  ");
            sb.append(" t_project a  ");
            sb.append(" INNER JOIN t_project_scheduling b ON a.uuid = b.project_uuid  ");
            sb.append(" INNER JOIN t_user creater ON creater.id = a.createUid  ");
            sb.append(" INNER JOIN t_user scheduling ON scheduling.id = b.createUid  ");
            sb.append(" LEFT JOIN t_work_order_info c ON a.uuid = c.dd_id  ");
            sb.append(" LEFT JOIN t_user workorder ON workorder.id = c.maintain_id  ");
            sb.append("  where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')  and b.end_time<now() and a.project_status<>'07' ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));
            if (!StringUtils.isEmpty(project_uuid)) {
                sb.append("  and a.uuid  = '").append(project_uuid).append("' ");
            }

            List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});

            //循环发送短信提醒
            for (int i = 0; i < list.size(); i++) {
                String creater_phone = (String) list.get(i).get("creater_phone");
                String workorder_phone = (String) list.get(i).get("workorder_phone");
                String scheduling_phone = (String) list.get(i).get("scheduling_phone");
                String projectName = (String) list.get(i).get("project_name");
                String time = (String) list.get(i).get("end_time");
                if (!StringUtils.isEmpty(creater_phone)) {
                    SMSUtil.sendProjectOvertimeRemind(creater_phone, projectName, time);
                }
                if (!StringUtils.isEmpty(workorder_phone)) {
                    SMSUtil.sendProjectOvertimeRemind(workorder_phone, projectName, time);
                }
                if (!StringUtils.isEmpty(scheduling_phone)) {
                    SMSUtil.sendProjectOvertimeRemind(scheduling_phone, projectName, time);
                }
            }

            //res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getWxInfo(Connection conn, String wx_id, String manufacture_id_ope) {
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();
        try {
            //发送项目超时提醒
            res.setRetCode(9);
            res.setRetMessage("没有查询到该无线设备信息");
            sb.delete(0, sb.length());
            sb.append(" SELECT a.protocol_id,a.manufacture_id,if(b.sb_id is null,'0','1') isBind from is_wxsb a LEFT JOIN is_equipment_info b on a.wx_id=b.wx_id where a.wx_id=? ");
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{wx_id});
            if (list.size() == 1) {
                String isBind = (String) list.get(0).get("isBind");
                String manufacture_id = (String) list.get(0).get("manufacture_id");
                if ("1".equals(isBind)) {
                    res.setRetCode(9);
                    res.setRetMessage("该无线模块已经绑定");
                    return res;
                }

                if (!manufacture_id.equals(manufacture_id_ope)) {
                    res.setRetCode(9);
                    res.setRetMessage("该无线模块不属于当前设备公私");
                    return res;
                }

                res.setData(list.get(0));
                res.setRetCode(0);
                res.setRetMessage("OK");

            }

        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getProjectInfo(Connection conn, String uuid) {
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();
        try {
            //发送项目超时提醒
            sb.delete(0, sb.length());
            sb.append(" SELECT * from t_project where uuid=? ");
            List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{uuid});
            if (list.size() == 1) {
                res.setData(list.get(0));
                res.setRetCode(0);
                res.setRetMessage("OK");
            }

        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData addBulidLog(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        param.put("tableName", "t_project_bulid_log");
        try {
            String logNo = (String) param.get("logNo");
            if (StringUtils.isEmpty(logNo)) {
                logNo = DBUtils.getDjbh(req.getUinfo().getCompanyId(), "LOG");
                param.put("logNo", logNo);
            }

            param.put("company_id", req.getUinfo().getCompanyId());

            String uuid = (String) param.get("uuid");
            if (StringUtils.isEmpty(uuid)) {
                res = insertTableData(conn, req);
                //插入项目记录表
                if (res.getRetCode() != 0) {
                    return res;
                }
                //插入日志
                String memo = "记录施工日志";
                insertProjectLog(conn, req, res.getKey(), "5", memo, "记录了" + memo);

            } else {
                param.put("keys", "uuid");
                res = updateTableData(conn, req);
            }

            if (res.getRetCode() == 0) {
                res.setRetCode(0);
                res.setRetMessage("OK");
            }
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {
        }
        return res;
    }

    public PageData getBulidList(Connection conn, DataReq req) {
        StringBuffer sb = new StringBuffer();
        List<Map<String, Object>> list = null;
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        try {
            String project_uuid = (String) param.get("project_uuid");
            sb.append(" SELECT b.uuid,a.project_name,b.logNo,b.personnel,b.personnelnum,b.remark,b.images,b.finishRate,date_format(b.createTime,'%Y-%m-%d %T') createTime,a.createUid,c.nickName,b.weather from t_project a INNER JOIN t_project_bulid_log b on a.uuid=b.project_uuid LEFT JOIN t_user c on a.createUid=c.id where a.uuid = ? ");
            sb.append(" ORDER BY b.createTime desc ");
            list = queryForList(conn, sb.toString(), new Object[]{project_uuid});

            for (int i = 0; i < list.size(); i++) {
                String images = (String) list.get(i).get("images");
                if (!StringUtils.isEmpty(images)) {
                    String[] imageList = images.split(";", -1);
                    List<String> imgURL = new ArrayList<String>();
                    for (int k = 0; k < imageList.length; k++) {
                        String str = imageList[k];
                        imgURL.add(OssUtil.getImgSignURL(str, 0));

                    }
                    list.get(i).put("imageList", imgURL);
                }
            }
            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(list);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getWeather(Connection conn, DataReq req) throws SQLException {
        StringBuffer sb = new StringBuffer();
        List<Map<String, Object>> list = null;
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        try {

            String area_code = (String) param.get("area_code");
            if (StringUtils.isEmpty(area_code)) {
                res.setRetCode(9);
                res.setRetMessage("请输入查询的区域代码");
                return res;
            }

            if (!StringUtils.isEmpty(area_code) && area_code.length() >= 6) {
                area_code = area_code.substring(0, 6);
            }
            sb.append(" SELECT CONCAT(a.text_desc,'   风力：',a.windDir,a.windScale,'   温度：',a.temp,'   湿度：',a.humidity) weather,a.temp,a.text_desc,a.windDir,a.windScale,a.humidity from pre_weateher a INNER JOIN pre_location b on a.city_num=b.city_num where b.xzqhdm like concat(?,'%') ORDER BY tdate desc limit 1 ");
            list = this.queryForList(conn, sb.toString(), new Object[]{area_code});
            if (list.size() == 0 && area_code.length() >= 4) {
                area_code = area_code.substring(0, 4);
                list = this.queryForList(conn, sb.toString(), new Object[]{area_code});
            }
            if (list.size() == 0 && area_code.length() >= 2) {
                area_code = area_code.substring(0, 2);
                list = this.queryForList(conn, sb.toString(), new Object[]{area_code});
            }
            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(list);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }
        return res;
    }

    public PageData getPreWeatherList(Connection conn, DataReq req) throws SQLException {
        StringBuffer sb = new StringBuffer();
        List<Map<String, Object>> list = null;
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        try {

            sb.append(" SELECT date_format(a.tdate,'%Y-%m-%d %T') createTime ,a.temp,a.text_desc,a.windDir,concat(a.windScale,'级') windScale,a.humidity,a.pop from pre_weateher a INNER JOIN pre_location b on a.city_num=b.city_num where b.xzqhdm = '110114000000' ORDER BY tdate desc limit 72 ");
            list = this.queryForList(conn, sb.toString(), new Object[]{});
            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(list);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }
        return res;
    }

    //用户签到
    public PageData userSign(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        param.put("tableName", "t_sign");
        try {
            String linkType = (String) param.get("linkType");
            String linkid = (String) param.get("linkid");
            String signType = (String) param.get("signType");
            String remark = (String) param.get("remark");
            if ("0".equals(signType)) {
                req.getReqData().put("source_longitude", req.getReqData().get("longitude"));
                req.getReqData().put("source_latitude", req.getReqData().get("latitude"));
                req.getReqData().put("source_address", req.getReqData().get("address"));
                req.getReqData().put("sign_start_time", DBUtils.getCurTime());
            } else {
                req.getReqData().put("dest_longitude", req.getReqData().get("longitude"));
                req.getReqData().put("dest_latitude", req.getReqData().get("latitude"));
                req.getReqData().put("dest_address", req.getReqData().get("address"));
                req.getReqData().put("sign_end_time", DBUtils.getCurTime());
            }

            param.put("company_id", req.getUinfo().getCompanyId());

            String uuid = (String) param.get("uuid");
            if (StringUtils.isEmpty(uuid)) {
                res = insertTableData(conn, req);
                //插入项目记录表
                if (res.getRetCode() != 0) {
                    return res;
                }
            } else {
                param.put("keys", "uuid");
                res = updateTableData(conn, req);
            }


            if (res.getRetCode() == 0) {
                res.setRetCode(0);
                res.setRetMessage("OK");
            }

            if ("1".equals(signType)) {//如果是到达，填写拜访内容
                if (StringUtils.isEmpty(linkid) && !"01".equals(linkType)) {
                    res.setRetCode(9);
                    res.setRetMessage("关联ID不能为空！");
                    return res;
                }
                req.getReqData().put("uuid", null);
                req.getReqData().put("remark", remark);

                switch (linkType) {
                    case "01"://地推
                        break;
                    case "02"://拜访客户

                        req.getReqData().put("customerId", linkid);
                        req.getReqData().put("visittype", "02");
                        req.getReqData().put("visittime", DBUtils.getCurTime());
                        req.getReqData().put("visitaddress", req.getReqData().get("address"));
                        req.getReqData().put("visitcontent", req.getReqData().get("remark"));
                        System.out.println(req.getReqData().get("visitcontent"));
                        res = customerDaoImpl.saveCustomerClaim(req);
                        if (res.getRetCode() != 0) {
                            return res;
                        }
                        break;
                    case "03"://施工签到
                        String work_order_id = linkid;
                        req.getReqData().put("work_order_id", work_order_id);
                        req.getReqData().put("status", "05");
                        req.getReqData().put("describe", "已到达");
                        req.getReqData().put("maintain_id", req.getUinfo().getId());
                        res = changeOrderStatus(conn, req);
                        if (res.getRetCode() != 0) {
                            return res;
                        }
                        break;
                    case "04"://售后签到

                        break;
                    default:
                        break;
                }
            }


        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();

        } finally {

        }
        return res;
    }

    public PageData getSignList(Connection conn, DataReq req) {
        StringBuffer sb = new StringBuffer();
        StringBuffer condition = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        List<Map<String, Object>> list = null;
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        try {
            List<Object> pm = new ArrayList<Object>();
            pm.add(req.getUinfo().getCompanyId());

            String uuid = (String) req.getReqData().get("uuid");
            if (!StringUtils.isEmpty(uuid)) {
                pm.add(uuid);
                condition.append(" and a.uuid=? ");
            }

            sb.append(" SELECT a.uuid,a.signType,a.linkid,a.linktype,a.signName, date_format(a.sign_start_time,'%Y-%m-%d %T') sign_start_time,date_format(a.sign_end_time,'%Y-%m-%d %T')  sign_end_time,a.source_longitude,a.source_latitude,a.dest_longitude,a.dest_latitude,a.dest_address,a.source_address,a.remark,a.tel,a.images,date_format(a.createTime,'%Y-%m-%d %T') createTime,a.createUid,b.name linktypeName from t_sign a INNER JOIN t_param b on a.linkType=b.`value` and b.param_type='103'  ");
            sb.append("  where a.company_id like concat(?,'%')   ");
            //if("01".equals(req.getUinfo().getUserType())) { //如果是安装人员，那么看自己的单据
            sb.append("  and  a.createUid=").append(req.getUinfo().getId());
            //}
            sb.append(condition);

            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, pm.toArray());
            sb.append(" ORDER BY a.createTime desc ");
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            list = queryForList(conn, sb.toString(), pm.toArray());

            if (!StringUtils.isEmpty(uuid)) {
                for (int i = 0; i < list.size(); i++) {
                    String images = (String) list.get(i).get("images");
                    if (!StringUtils.isEmpty(images)) {
                        String[] imageList = images.split(";", -1);
                        List<String> imgURL = new ArrayList<String>();
                        for (int k = 0; k < imageList.length; k++) {
                            String str = imageList[k];
                            imgURL.add(OssUtil.getImgSignURL(str, 0));

                        }
                        list.get(i).put("imageList", imgURL);
                    }

                    String sign_start_time = (String) list.get(i).get("sign_start_time");
                    String sign_end_time = (String) list.get(i).get("sign_end_time");
                    list.get(i)
                            .put("posList", getPositionList(conn, req.getUinfo()
                                    .getId(), sign_start_time, sign_end_time));
                }
            }

            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(list);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }


    public PageData getSignObjectList(Connection conn, DataReq req) {
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        List<Map<String, Object>> list = null;
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        try {
            String linkType = (String) param.get("linkType");
            //默认查询客户
            sb.delete(0, sb.length());
            sb.append(" SELECT uuid ,customername,address,phone, personincharge contact from customer a ");
            sb.append("  where a.companyid like concat(?,'%')   ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                    .getAreacode(), "a.xzqhdm", new String[]{"a.createUid", req.getUinfo().getId()}));


            if ("03".equals(linkType)) {//工单
                sb.delete(0, sb.length());
                sb.append("SELECT  b.work_order_id uuid,a.project_name customername,a.project_address address,b.contact_phone phone,b.contact from t_project a INNER JOIN t_work_order_info b on a.uuid=b.dd_id where 1=1 "); // b.`status`='04'
                sb.append("  and a.company_id like concat(?,'%')   ");
                sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo()
                        .getAreacode(), "a.area_code", new String[]{"a.createUid", req.getUinfo().getId()}));
                if ("01".equals(req.getUinfo().getUserType())) { //如果是安装人员，那么看自己的单据
                    sb.append("  and (b.maintain_id =")
                            .append(req.getUinfo().getId())
                            .append(" or b.createUid=")
                            .append(req.getUinfo().getId())
                            .append(")");
                }
            }

            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{req.getUinfo().getCompanyId()});
            sb.append(" ORDER BY a.createTime desc ");
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(list);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData projectReport(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        param.put("tableName", "t_project_reporting");
        try {
            param.put("company_id", req.getUinfo().getCompanyId());
            String uuid = (String) param.get("uuid");
            if (StringUtils.isEmpty(uuid)) {
                res = insertTableData(conn, req);
                //插入项目记录表
                if (res.getRetCode() != 0) {
                    return res;
                }
            } else {
                param.put("keys", "uuid");
                res = updateTableData(conn, req);
            }


            if (res.getRetCode() == 0) {
                res.setRetCode(0);
                res.setRetMessage("OK");
            }


        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();

        } finally {
        }
        return res;
    }

    public PageData getProjectReportList(Connection conn, DataReq req) {
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        List<Map<String, Object>> list = null;
        PageData res = new PageData();
        try {
            sb.append(" select d.xzqhmc,a.shzt,a.shyj,c.name shztname,a.uuid,company_id,e.companyName,a.project_name,manufacture_id,manufacture_name," + "part_a_name,part_a_contact,part_a_contact_phone,part_b_name,part_b_contact,part_b_contact_phone," + "area_code,project_address,project_type,investment_scale,build_area,planned_start_time,planned_end_time," + "remark,images,createUid,date_format(a.createTime,'%Y-%m-%d %T') createTime," + "modifyUid,date_format(a.modifyTime,'%Y-%m-%d %T') modifyTime,b.name project_type_name " + " from t_project_reporting a " + " INNER JOIN t_param b on a.project_type=b.`value` and b.param_type='92'" + " left JOIN t_param c on a.shzt=c.`value` and c.param_type='108'" + " left JOIN t_xzqh d on a.area_code=d.xzqhdm  " + " left JOIN t_company e on a.company_id=e.companyId ");
            sb.append(" where (a.company_id like concat(?,'%') or a.manufacture_id='")
                    .append(req.getUinfo().getCompanyId())
                    .append("')   ");
            if ("01".equals(req.getUinfo().getUserType())) { //如果是安装人员，那么看自己的单据
                sb.append(" and  a.createUid=").append(req.getUinfo().getId());
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_name"))) {
                sb.append(" and a.project_name like '%").append(req.getReqData().get("project_name")).append("%' ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("project_type"))) {
                sb.append(" and a.project_type='").append(req.getReqData().get("project_type")).append("' ");

            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("start_date"))) {
                sb.append("  and a.createTime >=  '").append(req.getReqData().get("start_date")).append("' ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("end_date"))) {
                sb.append("  and a.createTime <=  '").append(req.getReqData().get("end_date")).append(" 23:59:59' ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("shzt"))) {
                sb.append(" and a.shzt='").append(req.getReqData().get("shzt")).append("' ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("checkType"))) {
                if ("1".equals(req.getReqData().get("checkType"))) {
                    sb.append(getChecking(conn, req));
                }
            }
            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{req.getUinfo().getCompanyId()});
            sb.append(" ORDER BY a.createTime desc ");
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            //System.out.println(sb);
            //System.out.println(req.getUinfo().getCompanyId());

            for (int i = 0; i < list.size(); i++) {
                String images = (String) list.get(i).get("images");
                if (!StringUtils.isEmpty(images)) {
                    String[] imageList = images.split(";", -1);
                    List<String> imgURL = new ArrayList<String>();
                    for (int k = 0; k < imageList.length; k++) {
                        String str = imageList[k];
                        imgURL.add(OssUtil.getImgSignURL(str, 0));

                    }
                    list.get(i).put("imageList", imgURL);
                }
            }
            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(list);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public StringBuffer getChecking(Connection conn, DataReq req) {
        StringBuffer sbcheck = new StringBuffer();
        Map<String, Object> param = req.getReqData();
        sbcheck.append(" and ( 1=1 ");
        if (!StringUtils.isEmpty((String) param.get("project_name_check"))) {
            sbcheck.append(" and a.project_name like '%")
                    .append(req.getReqData().get("project_name_check"))
                    .append("%' ");
        }
        if (!StringUtils.isEmpty((String) param.get("part_a_name_check"))) {
            sbcheck.append(" and a.part_a_name like '%")
                    .append(req.getReqData().get("part_a_name_check"))
                    .append("%' ");
        }
        if (!StringUtils.isEmpty((String) param.get("part_a_contact_check"))) {
            sbcheck.append(" and a.part_a_contact like '%")
                    .append(req.getReqData().get("part_a_contact_check"))
                    .append("%' ");
        }
        if (!StringUtils.isEmpty((String) param.get("part_a_contact_phone_check"))) {
            sbcheck.append(" and a.part_a_contact_phone like '%")
                    .append(req.getReqData().get("part_a_contact_phone_check"))
                    .append("%' ");
        }
        if (!StringUtils.isEmpty((String) param.get("part_b_name_check"))) {
            sbcheck.append(" and a.part_b_name like '%")
                    .append(req.getReqData().get("part_b_name_check"))
                    .append("%' ");
        }
        if (!StringUtils.isEmpty((String) param.get("part_b_contact_check"))) {
            sbcheck.append(" and a.part_b_contact like '%")
                    .append(req.getReqData().get("part_b_contact_check"))
                    .append("%' ");
        }
        if (!StringUtils.isEmpty((String) param.get("part_b_contact_phone_check"))) {
            sbcheck.append(" and a.part_b_contact_phone like '%")
                    .append(req.getReqData().get("part_b_contact_phone_check"))
                    .append("%' ");
        }
        if (!StringUtils.isEmpty((String) param.get("investment_scale_check"))) {
            sbcheck.append(" and a.investment_scale like '%")
                    .append(req.getReqData().get("investment_scale_check"))
                    .append("%' ");
        }
        if (!StringUtils.isEmpty((String) param.get("xzqhmc_check"))) {
            sbcheck.append(" and d.xzqhmc like '%").append(req.getReqData().get("xzqhmc_check")).append("%' ");
        }
        if (!StringUtils.isEmpty((String) param.get("project_address_check"))) {
            sbcheck.append(" and a.project_address like '%")
                    .append(req.getReqData().get("project_address_check"))
                    .append("%' ");
        }
        if (!StringUtils.isEmpty((String) param.get("project_type_check"))) {
            sbcheck.append(" and a.project_type = '").append(req.getReqData().get("project_type_check")).append("' ");
        }
        if (!StringUtils.isEmpty((String) param.get("build_area_check"))) {
            sbcheck.append(" and a.build_area like '%").append(req.getReqData().get("build_area_check")).append("%' ");
        }
        if (!StringUtils.isEmpty((String) param.get("remark_check"))) {
            sbcheck.append(" and a.remark like '%").append(req.getReqData().get("remark_check")).append("%' ");
        }
        sbcheck.append(" ) ");
        return sbcheck;
    }

    public PageData getPointData(Connection conn, DataReq req) {
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        List<Map<String, Object>> list = null;
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        try {
            String month = (String) req.getReqData().get("month");
            String edate = DBUtils.getDate(DBUtils.getLastDayOfMonth(month), 0) + " 23:59:59";
            String sdate = month + "-01";
            sb.append(" SELECT a.`name`,IFNULL(sum(b.points) ,0) points from t_param a  LEFT JOIN (select point_type2 point_type,points from t_user_points where auditStatus='1' and  uid=? and summaryTime>=? and summaryTime<=? ) b on a.`value`=b.point_type where  a.param_type='106'  ");
            sb.append(" 		GROUP BY a.`name`  ");
            sb.append(" 		ORDER BY a.`value`  ");
            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getId(), sdate, edate});
            BigDecimal sum = new BigDecimal("0");
            for (int i = 0; i < list.size(); i++) {
                sum = sum.add(new BigDecimal((String) list.get(i).get("points").toString()));
            }
            list.get(0).put("sum", sum.toString());

            LinkedHashMap<String, Object> extData = new LinkedHashMap<String, Object>();
            extData.put("top", list);


            //获取明细数据
            sb.delete(0, sb.length());
            sb.append(" SELECT b.`name`,a.points,a.title,a.sub_title, date_format(a.summaryTime,'%d日 %T') summaryTime,a.link_type,a.link_id from t_user_points a LEFT JOIN t_param b on b.`value`=a.point_type2 and b.param_type='106' where auditStatus='1' and uid=? and summaryTime>=? and summaryTime<=?   ");
            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{req.getUinfo().getId(), sdate, edate});
            sb.append(" ORDER BY a.createTime desc ");
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getId(), sdate, edate});

            res.setExtData(extData);
            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(list);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getLevelParam(Connection conn, DataReq req) throws SQLException {
        StringBuffer sb = new StringBuffer();
        PageData res = new PageData();
        String param_type = "01";
        try {
            sb.delete(0, sb.length());
            sb.append(" SELECT `name`,`value`,length(`value`) len from t_param_level where param_type='01' and  length(`value`)=2   ORDER BY  `value` ");
            List<Map<String, Object>> dataList = queryForList(conn, sb.toString(), new Object[]{});

            for (int i = 0; i < dataList.size(); i++) {
                Map<String, Object> map = dataList.get(i);
                String preCode = (String) map.get("value");
                int len = 4;

                List<Map<String, Object>> sublist = getList(conn, param_type, preCode, len);
                for (int k = 0; k < sublist.size(); k++) {
                    Map<String, Object> e = sublist.get(k);
                    preCode = (String) e.get("value");
                    len = 6;
                    List<Map<String, Object>> sublist2 = getList(conn, param_type, preCode, len);
                    e.put("list", sublist2);
                }
                map.put("list", sublist);
            }

            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(dataList);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }

        return res;
    }

    public List<Map<String, Object>> getList(Connection conn, String param_type, String preCode,
            int len) throws SQLException {
        StringBuffer sb = new StringBuffer();
        List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
        try {
            sb.delete(0, sb.length());
            sb.append(" SELECT `name`,`value` from t_param_level where param_type='")
                    .append(param_type)
                    .append("' and `value` like '")
                    .append(preCode)
                    .append("%'  and length(`value`)=")
                    .append(len)
                    .append("  ORDER BY  `value`  ");
            dataList = queryForList(conn, sb.toString(), new Object[]{});
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }
        return dataList;

    }

    //获取用户经纬度信息
    public List<Map<String, Object>> getPositionList(Connection conn, String uid, String startdate,
            String enddate) throws SQLException {
        StringBuffer sb = new StringBuffer();
        List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
        try {
            sb.delete(0, sb.length());
            sb.append(" SELECT `longitude`,`latitude` from t_user_position where  createUid=? and createTime>? and createTime<? order by createTime ");
            dataList = queryForList(conn, sb.toString(), new Object[]{uid, startdate, enddate});
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }
        return dataList;

    }


    public PageData addPointsRule(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        try {
            String rule_name = (String) param.get("rule_name");

            JSONArray point_type2 = (JSONArray) param.get("point_type2");
            JSONArray point_type4 = (JSONArray) param.get("point_type4");
            JSONArray point_type6 = (JSONArray) param.get("point_type6");
            JSONArray point_unit = (JSONArray) param.get("point_unit");
            JSONArray points = (JSONArray) param.get("points");
            JSONArray uuid = (JSONArray) param.get("uuid");
            //JSONArray isNeedAudit=(JSONArray)param.get("isNeedAudit");


            param.put("company_id", req.getUinfo().getCompanyId());

            String No = (String) param.get("No");
            if (StringUtils.isEmpty(No)) {
                param.put("No", DBUtils.getDjbh(req.getUinfo().getCompanyId(), "NO"));
                for (int i = 0; i < point_type2.size(); i++) {
                    param.put("tableName", "t_user_points_rule");
                    param.put("point_type2", (String) point_type2.get(i));
                    param.put("point_type4", (String) point_type4.get(i));
                    param.put("point_type6", (String) point_type6.get(i));
                    param.put("point_unit", (String) point_unit.get(i));
                    param.put("points", (String) points.get(i));
                    //param.put("isNeedAudit",(String) isNeedAudit.get(i));
                    param.put("uuid", uuid.get(i));
                    res = insertTableData(conn, req);
                    if (res.getRetCode() != 0) {
                        return res;
                    }
                }
                //插入项目记录表
                if (res.getRetCode() != 0) {
                    return res;
                }
            } else {
                param.put("keys", "uuid");
                for (int i = 0; i < point_type2.size(); i++) {
                    param.put("tableName", "t_user_points_rule");
                    param.put("point_type2", point_type2.get(i));
                    param.put("point_type4", point_type4.get(i));
                    param.put("point_type6", point_type6.get(i));
                    param.put("point_unit", point_unit.get(i));
                    param.put("points", points.get(i));
                    //param.put("isNeedAudit",(String) isNeedAudit.get(i));
                    param.put("uuid", uuid.get(i));
                    res = updateTableData(conn, req);
                    if (res.getRetCode() != 0) {
                        return res;
                    }
                }
            }

            if (res.getRetCode() == 0) {
                res.setRetCode(0);
                res.setRetMessage("OK");
            }

        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();

        } finally {

        }
        return res;
    }

    public PageData getRuleList(Connection conn, DataReq req) {
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        StringBuffer condition = new StringBuffer();
        List<Map<String, Object>> list = null;
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        try {
            List<Object> pm = new ArrayList<Object>();
            pm.add(req.getUinfo().getCompanyId());
            String No = (String) param.get("No");
            String point_type2 = (String) param.get("point_type2");
            if (!StringUtils.isEmpty(point_type2)) {
                pm.add(point_type2);
                condition.append(" and  a.`point_type2` = ? ");
            }

            String start_date = (String) param.get("start_date");
            String end_date = (String) param.get("end_date");
            if (!StringUtils.isEmpty(start_date)) {
                pm.add(start_date);
                condition.append(" and  date(a.createTime) >= ? ");
            }
            if (!StringUtils.isEmpty(end_date)) {
                pm.add(end_date);
                condition.append(" and  date(a.createTime) <= ? ");
            }


            if (!StringUtils.isEmpty(No)) {
                pm.add(No);
                condition.append(" and  a.`No` = ? ");
            }
            sb.append(" SELECT a.isNeedAudit,a.uuid,a.rule_name,a.`No`,a.point_type2,a.point_type4,a.point_type6,a.point_unit,a.points,a.createUid,b.`name` point_type2_name,c.`name` point_type4_name,d.`name` point_type6_name,u1.nickName,date_format(a.createTime,'%Y-%m-%d %T') createTime from t_user_points_rule a  ");
            sb.append(" INNER JOIN t_param_level b on a.point_type2=b.`value` and b.param_type='01'  ");
            sb.append(" INNER JOIN t_param_level c on a.point_type4=c.`value` and c.param_type='01'  ");
            sb.append(" INNER JOIN t_param_level d on a.point_type6=d.`value` and d.param_type='01'  ");
            sb.append(" INNER JOIN t_user u1 on a.createUid=u1.id  ");
            sb.append("  where a.company_id like concat(?,'%')  ");
            sb.append(condition);

            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, pm.toArray());
            sb.append(" ORDER BY a.createTime desc ");
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            list = queryForList(conn, sb.toString(), pm.toArray());

            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(list);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getPointList(Connection conn, DataReq req) {
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        StringBuffer condition = new StringBuffer();
        List<Map<String, Object>> list = null;
        PageData res = new PageData();
        try {
            String startDate = (String) req.getReqData().get("startDate");
            String endDate = (String) req.getReqData().get("endDate");

            List<Object> pm = new ArrayList<Object>();
            pm.add(req.getUinfo().getCompanyId());
            if (!StringUtils.isEmpty(startDate)) {
                pm.add(startDate);
                condition.append(" and summaryTime>=? ");
            }
            if (!StringUtils.isEmpty(endDate)) {
                pm.add(endDate);
                condition.append(" and summaryTime<=? ");
            }

            //获取明细数据
            sb.delete(0, sb.length());
            sb.append("SELECT a.uuid,a.rule_uuid,a.basenum,b.`name`,a.points,a.title,a.sub_title, date_format(a.summaryTime,'%Y-%m-%d %T') summaryTime,a.link_type,a.link_id,a.auditStatus,a.auditComments,a.auditUid,a.uid,u1.nickName username,u2.nickName auditName,pb.`name` point_type2_name,pc.`name` point_type4_name,pd.`name` point_type6_name,rule.point_unit,rule.points rule_point from t_user_points a ");
            sb.append(" INNER JOIN t_user u1 on u1.id = a.uid");

            sb.append(" INNER JOIN t_user_points_rule rule ON rule.uuid = a.rule_uuid ");

            sb.append(" INNER JOIN t_param_level pb on rule.point_type2=pb.`value` and pb.param_type='01'  ");
            sb.append(" INNER JOIN t_param_level pc on rule.point_type4=pc.`value` and pc.param_type='01'  ");
            sb.append(" INNER JOIN t_param_level pd on rule.point_type6=pd.`value` and pd.param_type='01'  ");


            sb.append(" LEFT JOIN t_user u2 on u2.id = a.auditUid");
            sb.append(" LEFT JOIN t_param b on b.`value`=a.point_type2 and b.param_type='106' ");
            sb.append("  where u1.companyId like concat(?,'%')  ");
            sb.append(condition);

            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, pm.toArray());
            sb.append(" ORDER BY a.createTime desc ");
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            list = queryForList(conn, sb.toString(), pm.toArray());
            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(list);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }


    //审核积分
    public PageData audituserPoint(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        param.put("tableName", "t_user_points");
        param.put("keys", "uuid");
        try {
            String uuid = (String) param.get("uuid");
            String auditStatus = (String) param.get("auditStatus");
            String auditComments = (String) param.get("auditComments");

            if (StringUtils.isEmpty(uuid)) {
                res.setRetCode(9);
                res.setRetMessage("单据ID不能为空");
                return res;
            }

            if (StringUtils.isEmpty(auditStatus)) {
                res.setRetCode(9);
                res.setRetMessage("审核状态不能为空");
                return res;
            }

            param.put("auditTime", DBUtils.getCurTime());
            param.put("auditUid", req.getUinfo().getId());
            res = updateTableData(conn, req);

            if (res.getRetCode() == 0) {
                res.setRetCode(0);
                res.setRetMessage("OK");
            }

        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();

        } finally {

        }
        return res;
    }

    //审核积分
    public PageData addUserPosition(Connection conn, DataReq req) {
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();
        Map<String, Object> param = req.getReqData();
        try {
            param.put("distance", "0");
            sb.append(" select latitude,longitude from t_user_position where createUid=? order by id desc limit 1 ");
            List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getId()});
            if (list.size() > 0) {
                BigDecimal last_longitude = new BigDecimal(list.get(0).get("longitude").toString());
                BigDecimal last_latitude = new BigDecimal(list.get(0).get("latitude").toString());

                BigDecimal longitude = new BigDecimal((String) req.getReqData().get("longitude"));
                BigDecimal latitude = new BigDecimal((String) req.getReqData().get("latitude"));
                double d = DBUtils.GetDistance(last_longitude.doubleValue(), last_latitude.doubleValue(), longitude.doubleValue(), latitude.doubleValue());
                if (d < 100) {
                    //res.setRetCode(0);
                    //res.setRetMessage("距离小于100米不记录");
                    //return res ;
                }
                param.put("distance", String.valueOf(d));
            }

            param.put("tableName", "t_user_position");
            res = insertTableData(conn, req);
            if (res.getRetCode() == 0) {
                res.setRetCode(0);
                res.setRetMessage("OK");
            }

        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    @SuppressWarnings({"unchecked"})
    public PageData saveProjectReportShzt(Connection conn, DataReq req) {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        PageData sqlInfo = null;
        try {
            param.put("tableName", "t_project_reporting");
            param.put("keys", "uuid");
            sqlInfo = getUpdateSQL(conn, req);
            if (sqlInfo.getRetCode() != 0) {
                return sqlInfo;
            }
            final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            update(conn, sb_field.toString(), listData.toArray());

            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setKey(param.get("uuid").toString());
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {
        }
        return res;
    }

    public PageData getDevList(Connection conn, DataReq req) {
        StringBuffer sb = new StringBuffer();
        List<Map<String, Object>> list = null;
        PageData res = new PageData();
        try {
            String project_uuid = (String) req.getReqData().get("project_uuid");

            sb.delete(0, sb.length());
            sb.append(" SELECT a.wx_id,b.xmlxr,b.xmdz,b.xmlxrdh,a.sbxlh,c.sbxh,date_format(a.cjsj ,'%Y-%m-%d') install_date,date_format(a.expired_date ,'%Y-%m-%d') expired_date  from is_equipment_info a INNER JOIN is_install_info b on a.dd_id=b.dd_id INNER JOIN t_sblx c on a.sblx_id=c.sblx_id where  b.scddh=? ");
            sb.append(" ORDER BY a.cjsj desc ");
            list = this.queryForList(conn, sb.toString(), new Object[]{project_uuid});

            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }
        return res;
    }

    public PageData installDev(Connection conn, DataReq req) {
        Map<String, Object> param = req.getReqData();
        HashMap<String, Object> param_dev = new LinkedHashMap<String, Object>();
        PageData res = new PageData();
        try {
            param_dev.putAll(param);

            //生成项目
            param.put("tableName", "t_project");
            param.put("project_id", DBUtils.getDjbh(req.getUinfo().getCompanyId(), "PRO"));
            param.put("company_id", req.getUinfo().getCompanyId());
            param.put("project_name", param.get("contact"));
            param.put("maintain_id", req.getUinfo().getId());

            if (StringUtils.isEmpty((String) param.get("contact"))) {
                res.setRetCode(9);
                res.setRetMessage("联系人不能为空！");
                return res;
            }

            if (StringUtils.isEmpty((String) param.get("contact_phone"))) {
                res.setRetCode(9);
                res.setRetMessage("联系电话不能为空！");
                return res;
            }

            if (StringUtils.isEmpty((String) param.get("longitude"))) {
                res.setRetCode(9);
                res.setRetMessage("经度不能为空！");
                return res;
            }


            if (StringUtils.isEmpty((String) param.get("latitude"))) {
                res.setRetCode(9);
                res.setRetMessage("纬度不能为空！");
                return res;
            }

            if (StringUtils.isEmpty((String) param.get("sblx_id"))) {
                res.setRetCode(9);
                res.setRetMessage("设备型号不能为空！");
                return res;
            }


            if (StringUtils.isEmpty((String) param.get("manufacture_id"))) {
                res.setRetCode(9);
                res.setRetMessage("厂家不能为空！");
                return res;
            }

            if (StringUtils.isEmpty((String) param.get("project_address"))) {
                res.setRetCode(9);
                res.setRetMessage("项目地址不能为空！");
                return res;

            }

            param.put("planned_start_time", DBUtils.getCurTime());
            param.put("planned_end_time", DBUtils.getCurTime());
            param.put("investment_scale", null); //投资规模
            param.put("project_status", "07");

            param.put("charge", param.get("contact"));
            if (StringUtils.isEmpty((String) param.get("area_code"))) {
                JSONObject addressJson = MapUtil.getGeoByAddress((String) param.get("project_address"));
                param.put("area_code", addressJson.get("adcode"));
                param_dev.put("area_code", addressJson.get("adcode"));
            }
            param.put("service_mode", "01");
            param.put("project_type", "01");
            param.put("project_level", "01");


            res = insertTableData(conn, req);
            if (res.getRetCode() != 0) {
                return res;
            }
            param.put("project_uuid", res.getKey());
            param_dev.put("project_uuid", res.getKey());

            HashMap<String, Object> e = new LinkedHashMap<String, Object>();
            e.put("tableName", "t_project_record");
            e.put("project_uuid", res.getKey());
            e.put("project_status", "07");
            e.put("remark", "完成");
            req.setReqData(e);
            PageData resTmp = insertTableData(conn, req);
            if (resTmp.getRetCode() != 0) {
                return resTmp;
            }

            //项目排期
            e = new LinkedHashMap<String, Object>();
            e.put("tableName", "t_project_scheduling");
            e.put("project_uuid", res.getKey());
            e.put("start_time", DBUtils.getCurTime());
            e.put("end_time", DBUtils.getCurTime());
            req.setReqData(e);
            resTmp = insertTableData(conn, req);
            if (resTmp.getRetCode() != 0) {
                return resTmp;
            }

            //创建工单
            e.put("tableName", "t_work_order_info");
            e.put("work_order_id", DBUtils.getDjbh(req.getUinfo().getCompanyId(), "WOR"));
            e.put("company_id", req.getUinfo().getCompanyId());
            e.put("dd_id", param.get("project_uuid"));
            e.put("work_type_id", "1");
            e.put("contact", param.get("contact"));
            e.put("contact_phone", param.get("contact_phone"));
            e.put("address", param.get("project_address"));
            e.put("planned_start_time", null);
            e.put("planned_end_time", null);
            e.put("xzqhdm", param.get("area_code"));
            e.put("manufacture_id", param.get("manufacture_id"));
            e.put("describe", "创建安装工单");
            e.put("status", "06");
            e.put("maintain_id", req.getUinfo().getId());
            req.setReqData(e);
            resTmp = insertTableData(conn, req);
            if (resTmp.getRetCode() != 0) {
                return resTmp;
            }

            //插入工单记录
            e.put("describe", "工单完成"); //待接单
            resTmp = addWorkOrderRecord(conn, req);
            if (resTmp.getRetCode() != 0) {
                return resTmp;
            }


            //添加设备
            req.setReqData(param_dev);
            res = addOpeDev(conn, req);
            if (res.getRetCode() != 0) {
                return res;
            }

            if (res.getRetCode() == 0) {
                res.setRetCode(0);
                res.setRetMessage("OK");
            }
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {
        }
        return res;
    }


}